Automatic colour change for dates

[roskalov]

Hi everyone,


Hope somebody could help with my problem.

I have a list of contracts with Start and End dates. Those are in "Short Date" format. Using conditional formatting (or any other means) is it possible to set an automatic colour change for the cells? What I need is RED if it expired (i.e. the end date is before today), YELLOW (i.e. the end date is within 12 months from today) and GREEN (i.e. the end date is within more than 12 months from today).

The main thing is that the cells should automatically update their colour if necessary. For instance, the contract ends on July 17th 2018, it should be green now, but if I open the document on Monday, it should change to yellow.

To note, this is the shared spread sheet thus many functions are disabled.

Thank you very much indeed in advance.

Should you require the actual spread sheet, I can upload it too but was not sure if it is necessary.


Kind regards,

Vladislav

[davsth]

you have nearly done it yourself
if your end dates are in a1:A10, select this range, conditional formatting _use a formula to determine what cells to format, =a1>today()+365 then pick the colour green.

Repeat for a1>today() and choose yellow

repeat for a1>0 choose red

if the formats are in the order above you need to tick the stop is true box in the managing conditional formatting

[roskalov]

Thank you for clarifying. But as I said, it is a shared workbook. Hence conditional formatting is blocked. Is there any way to unblock that function?

[davsth]

I believe that if you set up the conditional formatting in the workbook when it is not shared. Then share it, the formatting should work in versions 2010 onwards, its just that it cant be edited