Forum Discussion
Color Code dates for expiration
I am creating a spread sheet at work with our yearly training.
I need to know how to use conditional formatting to show when employees are 30 days out for training due and when the yearly date is up.
2 Replies
Here's a general and good reference that explains Conditional Formatting. If you want to get specific guidance for your specific situation, you should share more details of how your data are organized, ideally in the form of a copy of your actual workbook--with the real names of people removed, along with any other identifiable data (like company identifiers). Post that on OneDrive or GoogleDrive with a link here that grants access.
In my experience, Conditional Formatting--especially if you're new to it--often takes a bit of trial and error, but that's also a good way to learn its quirks.
Let's say you have a range with the most recent training dates.
Select this range.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'less than or equal to' from the second drop down.
In the box next to it, enter the formula=EDATE(TODAY(), -11)
Click Format...
Activate the Fill tab.
Select - for example - yellow or orange as highlight color.
Click OK, then click OK again.Repeat, but with
=EDATE(TODAY(), -12)
and red as fill color.
