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.
Then I tested the main formula on an individual random cell (B5) and came up with the following formula that seemed to work fine.
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