Conditional Formate based on DATE?

[lpking2005]

hi,

I currently have a spreadsheet with loads of different cells that i have to input a different date into manually.

What im having trouble with is getting it to turn green when i put a new date in, but i need it to turn red a year later.

Any help would be much appreciated. THANKS.

 

[Richard Schollar]

Do you mean you want it to be green if the date is within the last year (from the current date) and red if not OR do you mean you want to keep a record of when the date has been entered into the cell and colour the date cell red after 1 year has passed (so essentially it doesn't matter what actual date is entered into the cell)?

The latter is more complicated and would require VBA.

 

[Andrew Poulsom]

Try the conditional formatting formulas:

Red =AND(ISNUMBER(A1),(TODAY()-A1)>365)
Green =ISNUMBER(A1)

Change the range reference to suit.

 

[lpking2005]

basically, i have a list of peoples names and training tasks.

I have to input a date on which they were trained into the correct cell, i need that cell to turn green for a new date that is inputted.
Then i need that cell to turn red a year later when they need refresher training.

I hope that helps more in understanding.

 

[lpking2005]

Many Thanks Andrew!! that worked brilliantly!!

Thanks again!