Conditional Formatting Based on Dates

Question

Hi All,

I have a spreadsheet full of compliance expiration dates. There are multiple worksheets in the same file. I would like to apply a conditional format so when the expiration date is a certain number of dates out, then it turns a certain color. I see the presets for 2- and 3-color scales, but the issues are that I want four colors, and I don't want the color to gradually change, as it's doing now (see scale below - I don't want that). I want the colors to change immediately at the following intervals:

60 days out - yellow

30 days out - orange

14 days out - red

0 days (or expired) - black with white text.

I think I'm on the right track with the conditional formatting, but this type of setting is beyond my knowledge. Please help!

Thank you very much!!

Amanda

Answer 1

Hi,

Try this

1. Click on the first cell (say cell A2) and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
2. In the Formula bar there, enter this formula

=AND(A2-TODAY()>0,A2-TODAY()<=14)

3. Click on Format > Fill > Red > OK/Apply
4. Write similar formulas for others as well
5. Copy cell A2, select the range of dates below > right click > Paste Special > Formats > OK

Hope this helps.

Answer 2

Hi,

The left aligned dates force me to assume that they are not actually dates (but text entries instead).  When you click on the Filter drop down of the Hull column, do you see all dates getting grouped by months and years?

Answer 3

Hi Ashish,

I've copied the formula where you've noted, and Excel has accepted the formula, but it doesn't seem to be working. I've pasted an example below - all of these cells have that formatting, and I've ordered them from top to bottom 0, 14, 30, 60. As you can see, the first date under Hull should be black with white text, and the last date under COI should be orange.

Do you have any idea why this isn't working? All cells are also formatted for "short date".

Thank you!

Amanda

COI	Hull	ABS	C.O.D.	Fire Extinguisher
4/28/2016	7/30/2015	N/A	1/31/2017	3/31/2016
6/30/2016	7/30/2017	N/A	1/31/2017	3/31/2016
	1/30/2018	N/A	4/30/2016
6/30/2017	6/1/2016	4/15/2016	3/31/2016	11/1/2016
N/A	11/1/2016	N/A	2/28/2016	3/31/2016
2/5/2016	3/21/2016	N/A	2/28/2016	3/31/2016

Answer 4

Hi JP,

I suspected as much, but I would need the actual formula, since I'm not really knowledgeable of this function.

Thank you,

Amanda

Answer 5

Hi Amanda,

You can do this with "use a formula" in CF. Something like:

=<cell-date>-today()<90 turn it yellow

=<cell-date>-today()<60 turn it orange

=<cell-date>-today()<30 turn it red