Formula for dates
Posted by Simone on February 15, 2002 1:48 AM
How can I get all the dates in a column to show red if they are more than two years old?
I have tried conditional formatting but that only highlights dates which are two years old from a set date.
They must only show red if they are two years old from the current date so this should change everytimme the spreadsheet is opened.
Posted by IML on February 15, 2002 6:18 AM
This would be perfect the use of edate, except analyis took pack formulas are not allowed in conditional formatting. So you could use the following under conditional formatting, formula option:
=A1<(NOW()-730)
but this doesn't account for leap years. which leaves us with the fairly ugly but accurate:
=DATE(YEAR(NOW())-2,MONTH(NOW()),DAY(NOW()))>A1
Good luck
Posted by DrDrew on February 15, 2002 6:31 AM
Cell Value Is, less than, =today()-732
Assuming you are using the 1900 dating system (default I believe), 732 represent in decimal form, 2 years in Excel. Viola.
Drew
