DATEDIF formula range definitions

[SpreadsheetSavage]

Hi all. I have this spreadsheet that use to to keep track on different property assets that I need to check on from time to time. Each asset has a dedicated row, and whenever I’ve checked the status of an asset and updated the information if needed, I mark the current date in the first column of the row, as an indicator that a checkup has been made at this date.

Ideally I should check up on each asset at least once a month, but the need for a check up often rises from other reasons also. As a result I regularly need to skim through all the individual dates (on the first column) to see how long has it been since my last checkup, which is painstaking since there are hundreds of assets listed.

So I’m trying to make a conditional formatting rule for this first “Date” column where the each cell checks against the current date and if the difference is more than a month, a custom style with a bright background color is applied to the date cell. This way I could see which asset rows are in need of a checkup with a glance.

First I made a cell on top of my sheet (C2) to display the current date with the following formula.

=TEXT(TODAY();"DDMMYYYY"). 

Then I tested the main formula on an individual random cell (B5) and came up with the following formula that seemed to work fine.

IF(DATEDIF(B5;C2;"m")>1)

However when I then tried to modify the above formula slightly to apply it for the “formula is” conditional formatting of the whole range of cells in the “Date” column (C), I hit a brick wall. I have tried the following variations but none of them work.

IF(DATEDIF(C3:C363;C2;"m")>1)
IF(DATEDIF($C$3:$C$363;C2;"m")>1)
IF(DATEDIF($C3:$C363;C2;"m")>1)

I’ve used similar range definitions on other conditional formulas before, and they’ve usually worked. I have difficult time wrapping my head around what I’ve done wrong here.

Any help would be much appreciated, if someone has the time

*Edited to mark the date column (C) for clarity

[JohnSUN]

a conditional formatting rule for this first “Date”

Sorry, which column contains the check dates? Column C? You used B5 in the test formula, which is a little confusing.

[SpreadsheetSavage]

Oh right, my bad. Yeah C is the actual date column, B is indeed used only for the test formula.

[JohnSUN]

Well, just try do it

[SpreadsheetSavage]

Interesting, didn’t realize LO smart features extended to conditional formats as well. This was a useful thing to learn alone.

This works with cells formatted as “date”, but sadly not with when formatted as “text”. It wouldn’t matter otherwise, but using “text” format for the date cells allows much faster typing, since you can enter dates in DDMMYYYY format (without . or / separators) using numpad only.

But it’s a partial solution nonetheless and something I can fall back to. Thank you.

Good point about the >1 at the end of the formula, though there’s some usage since I planned on later adding >2 and >3 versions of the formula, so I could get three alert stages for missing checkups (1 month=yellow, 2 months=orange, 3 months=red).

[JohnSUN]

using “text” format for the date cells allows much faster typing

This is your choice. You have every right to spend the efforts, time and resources of your computer and teach Calc to understand eight-digit strings as dates - it’s difficult, but it can be done. But if you simply accept the agreement condition “Date is not a string, but the number of days (as well as hours, minutes and seconds) have passed since midnight of the start date and the date value must be entered in a specific format” and develop the skill of abbreviated date entry (Ctrl+; to enter the current date or enter from 3 up to 8 characters in accordance with the Date acceptance patterns), you will save all this effort, time and resources.

[SpreadsheetSavage]

I don’t have any problems accepting the fundamentals of spreadsheet dates and all, but my goal here is simply to improve the efficiency of my asset checkup system. If I learn something new along the way, that’s just great and I’m grateful for it, but first things first.

The text format thing might sound petty, but the time it saves really adds up when you have hundreds of lines. I can keep my right hand on my mouse and left on the numpad while I go checking through the sheet.

teach Calc to understand eight-digit strings as dates - it’s difficult, but it can be done

Ok I’m interested. How would this happen, macros?

[JohnSUN]

Macro, of course. Formulas could help too, but it would be too cumbersome. Most likely it should be the inverse FORMAT() function - the parsed string and the template string, which indicates where “DD” in the original string, and where “YYYY”.

I can keep my right hand on my mouse…

If we are still talking about macro, then who prevents you from processing a double click on a cell with a date and replacing the value with the current date? That would also be a simplification of the process. You said that the current date marks the checked rows? Here’s the solution.

[SpreadsheetSavage]

Right. Unfortunately I haven’t done a single macro, so even though I can understand the gist of what you’re saying on how it could be solved it’s gonna be a long way for me.

processing a double click on a cell with a date and replacing the value with the current date

This is funny because one of my earliest experiments was to have an additional “check” column next to the the “date” column and have the “date” column cells be conditioned with IF on whether the “check” column had a single “1” typed, and when true have TODAY() printed on the “date” column. Of course this was silly since while true the “date” column would just always print today’s date on each and every day.

What you’re describing would pretty much be spot on perfect, obviously much more efficient than any typing solution. Again too bad that I don’t have experience on macros, but I assume this kind of macro wouldn’t be very complicated? I mean it’s just two components, trigger=double click & event=TODAY().

[JohnSUN]

I haven’t done a single macro

It doesn’t matter - most macros have been written for a long time. They just need to be found and used without worrying about how they are written.

much more efficient than any typing solution

Just yesterday I got a notification that the moderator has closed the old question. It’s just a lucky coincidence. Please see my answer to this question. This is not ideal - you have to click three times to enter the current date. And you’re right - it’s not very complicated, only 200 lines (of which 50 are comments)

[SpreadsheetSavage]

That’s a valid point. I’ve been curious about macros before and the functions they bring, but somehow regarded them “off-limits” since I couldn’t write them myself. Guess I’ll update my view.

Please see my answer

Sweet, I’ll try your macro right when I get a few days break. Installing it is probably simple but I want to do it step by step so I can learn at the same time. Actually three clicks might be even better, since it makes harder to accidentally enter the current date (and thus mark an asset as checked) which can be costly if it leads to me missing a major event!

Thank you for using your time to help me. If I get your macro to work it will indeed provide a superior solution to my root problem. Even more, if I get confident enough I might be able to use other macros too in the future.