Forum Discussion
Conditional Formatting using dates
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.
7 Replies
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 :).
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?
- Hi, Please see attached.
https://docs.google.com/spreadsheets/d/1zI3Qdaxw9yesSUVvlGePWuGOV4_EB3rP/edit?usp=sharing&ouid=107628387144989397156&rtpof=true&sd=trueThank 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'.
