Highlighting date cells based on day of viewing file

Hi,

I have a spreadsheet with a column F called 'Completion Date' and I would like to highlight the dates in that column Red/Amber/Green depending on the day that I open the file i.e Today

For example, Red - Passed dates and dates coming in next 2 weeks, Amber - Coming up in next 2-4 weeks, Green is up to 4-8 weeks in future.

For example today is 03/05/2022, so if the date in column F is 18/05/2022, then that cell would be highlighted Amber as it is 15 days in the future.

If I open the file Next week (10/05/2022), then the date 18/05/2022 in Column F would auto be highlighted Red because it is less than 2 weeks.


So is there a way to auto validate them depending on the day that you open the file?

Thanks!

Originally Posted by janmorris

select the range in column F

create the following:

for green:

Please Login or Register  to view this content.

for amber:

Please Login or Register  to view this content.

for red:

Please Login or Register  to view this content.

arrange them with green at the top, followed by amber, then red last

Will give it a try then report back.. thanks!

Originally Posted by janmorris

select the range in column F

create the following:

for green:

Please Login or Register  to view this content.

for amber:

Please Login or Register  to view this content.

for red:

Please Login or Register  to view this content.

arrange them with green at the top, followed by amber, then red last

Worked like a charm.. many thanks!