Hi... I have a spread sheet I would like the date to change colour as the date gets older.

So when I enter todays date 12/06/2017 it will be green, in 6 months time I would like this date to change to orange and then in a years time I would like it to turn red.



how do I set this up please? TIA

Use Conditional Formatting....

Select the cell and go to Home|Conditional Formatting, New Rule.

Select "format only cells that contain"

in the Rules description area...

Cell Value is >> Equal to >> =TODAY()

Click Format and choose from Fill tab.

Click Ok, click New Rule

Select "use a formula to determine which cells to format" then enter:

=AND(DATEDIF(A2,TODAY(),"m")>=6,DATEDIF(A2,TODAY() ,"m")<=12)

where A2 contains the date...

Click Format and choose from Fill tab.

Click Ok, click New rule and repeat as above with Rule

=DATEDIF(A2,TODAY(),"m")>12)

Thank you so much for getting back to me NBVC,

I have 2 working the first date and the 12 month date.

but the 6 month old date isn't working

=AND(DATEDIF(A2,TODAY(),"m")>=6,DATEDIF(A2,TODAY() ,"m")<=12)

the above formula doesn't do anything if the date is 6 months older than todays date....

Is there a way to make the colour stay in place for the full 6 months? So from today thru to 6months time it stays green. Then from 6month to 12 months its orange and then 12months old it stays red?

What am I doing wrong haha

Hi,

Sorry have been away for a few days...

For me it works.

The only change I would recommend is with the first formula... instead of just =TODAY(), use formula: =AND(A2<=TODAY(),DATEDIF(A2,TODAY(),"m")<6) this will keep it green until 6 months old...