What is formula to have an upcoming date change color?

S

Sunset

I have a spreadsheet that shows contract begin and end dates. I would like
the end date to highlight or change colors when it is two months away.
 
A

AKphidelt

Hey, try this... it should work by highlighting the cells that are within two
months away. Select the column that has the dates.... then go to

Format---> Conditional Formatting

Then change Condition 1 to----> Formula is

And in the Formula Box type

=AND(A1-TODAY()<60,A1-TODAY()>0)

REMEMBER**** you have to Change A1 to whereever your first date starts. So
if your dates are in column H... you will change the A1s to H1s, etc.
 
P

Peo Sjoblom

Select the cell you want to change, assume it is A2, do format conditional
formatting, select formula is and use

=DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY()))>=A2

click the format button and select the format you want

what do you want to happen when the date is less than today? If you want to
remove the formatting you can use

=AND(A2>=TODAY(),DATE(YEAR(TODAY()),MONTH(TODAY())+2,DAY(TODAY()))>=A2)
 
S

Sunset

Seems to be the right direction, although the boxes that highlighted either
have no information or are dated 12-30-2007. The column is the "H" beginning
with row "7"
 
A

AKphidelt

Try just doing it for the entire column starting with H1, because it works
perfectly on my spreadsheet. It only highlights values that are today through
2 months... and leaves all text and other dates alone.
 
S

Sunset

That didn't do it either, my cells with 5/31/2007 should have highlighted, I
only had one dated 6/30/2007 that highlighted out of 4, and still many with
N/As that highlighted.
 
P

Peo Sjoblom

Except that it won't work for all months since you assume that 2 months are
60 days whereas Jan and Feb are 59 or 60 or Jul and Aug are 62 meaning that
when today is for instance either 28, 29 or 30th December and the future
date is 02/28 it won't change the format.
 
S

Sunset

Thank you for your help

AKphidelt said:
Try just doing it for the entire column starting with H1, because it works
perfectly on my spreadsheet. It only highlights values that are today through
2 months... and leaves all text and other dates alone.
Click to expand...
 
Top