Conditional Formatting using dates

[Lennox2525]

Hi, 

Can anyone help me?

Using one column on a spreadsheet and conditional formatting I need to do the following for test certificates that are due/expired/no action required as below.

Green for anything within date & right up to the expiry date (day before).

Orange for expiry due date + 2 weeks

Red for once expiry has gone overdue (past 2 weeks from orange flag) until test has been taken & then reverts back to green.  

[#2]

@Lennox2525 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

@Lennox2525 

Use conditional formatting to highlight information

If I may add, here is a sample file with examples of date entries in conditional formatting.

If you don't get any further with this, as already mentioned by Hans Vogelaar , please insert a file.

If possible, additional information about the digital environment (Excel version, operating system, storage medium, etc.) would help :).

[#4]

Hi, Please see attached.

https://docs.google.com/spreadsheets/d/1zI3Qdaxw9yesSUVvlGePWuGOV4_EB3rP/edit?usp=sharing&ouid=10762...

[#5]

@Lennox2525 

Thank you.

Select the cells you want to format. It's OK to include empty cells.

Start by setting the fill color to green. That will be the default.

On the home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Lave the first drop-down set to 'Cell Value'.

Select 'less than or equal to' from the second drop-down.

Enter the formula =TODAY() in the box next to it.

Click Format...

Activate the Fill tab.

Select orange.

Click OK, then click OK again.

Repeat these steps, but with the formula =TODAY()-14 and red.

Finally, repeat them again, with 'equal to' instead of 'less than or equal to', with the formula ="" and 'No Color'.

[#6]

Hi @Hans Vogelaar 

That sounds great in practice, but its not giving me what I want. 

Today is 31/01/2023.

Anything that is 31/01/2023 and upto and including -14 days (17/01/2023) needs to be orange.

Anything that is 31/01/2023 and -15 days( 16/01/2023) and over  needs to be Red.

Anything from 01/02/2023 needs to be green.

I would be most grateful for a solution :)

[#7]

@Lennox2525 

See the attached version of your workbook.

[#8]

Thank you - a little tweaking and it works perfectly :)