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/