MS Excel conditional formatting by date formula does not work
I need to apply conditional formatting on cells containing dates (expiration dates of certificates). I want expired certificates in red, expiring withing a week orange, expiring within a month (30 days) in yellow and others unmarked (talking about background colour). But when I put formula
=A2-today()<0
all cells get filled red, regardless the date. When I change the number to 7 and select orange colour, they all cells get filled orange, again regardless of the date. If everything worked fine, the A2 cell should be orange right now. What do I do wrong? How shall I write the formula?
You can use the following functions:
DATEDIF
TODAY
This gives the number of days between today and the date in A2:
=DATEDIF(A2, TODAY(), "d")
So, you can use:
=DATEDIF($A2, TODAY(), "D") > 0
[select red color for this formatting]
=AND(DATEDIF(TODAY(), $A2, "D") >= 0, DATEDIF(TODAY(), $A2, "D") < 7)
[select orange color for this formatting]
=AND(DATEDIF(TODAY(), $A2, "D") >= 7, DATEDIF(TODAY(), $A2, "D") < 30)
[select yellow color for this formatting]
You can go to this link and search for the section called "Highlighting delays": https://www.microsoft.com/en-us/microsoft-365/blog/2013/04/04/using-conditional-formatting-to-highlight-dates-in-excel/