Conditional Formatting of Dates in Excel

问题:

I am trying to make conditional formatting for Dates in Excel. The cells should change the color depeding on when the Date is due. If it is due in less than 30 days it should be red. If it is due in less than 60 days it should be yellow. If it is due in less than 90 days it should be green.

If have following Formula =DATEDIF(TODAY();E:E;"d")<30as a conditional formatting rule for the start. The strange part is that it works if I have it in a cell. It returns the value true for the values in "E" that are due in less than 30 days. However I cannot apply this rule in conditional formatting. It does not give me an error. It simply does nothing...

Can this be solved with conditional formatting or should I write a VBA script?

回答1:

Ok, Excel does some wacky stuff that it thinks is helpful when you're trying to apply conditional formatting to a range (it's usually more of a hindrance than help).

Follow these steps:

1. Select the first cell with a date value in it, I'm assuming E2.
2. Click on Conditional Formatting -> New Rule.
3. Select "Use a formula to determine which cells to format".
4. Paste in the formula =DATEDIF(TODAY(),E2,"d")<30 and select whatever formatting options you want. Click OK.
5. Go back into conditional formatting by clicking on "Manage Rules".
6. In the Applies to field, change it to =E2:E200, or whatever the last row of the range is. Alternatively you can use a named range if you want to set up something a little more dynamic.

That rule now applies to the whole range.

回答2:

I used the following formulas:

=DATEDIF(TODAY(),E2,"d")<30
=DATEDIF(TODAY(),E2,"d")<60
=DATEDIF(TODAY(),E2,"d")<90