Conditional Formatting of Dates in Excel

0
votes

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?

2
vbaexcelms-office
Just use a regular If statement to determine it, I don't see why you'd need DatedIf. – BruceWayne
Instead of referring to E:E, select just one cell in the same row to apply it to and make it refer to the same specific row in that column, E1 for example. As long as you get rid of any $s that Excel automatically applies, then it will still be a relative reference and you can extend the range it applies to in the "Manage Rules" menu after you have gotten it working for one cell. – CactusCake
I will assume you have titles in the first row. So in E2 put the following conditional formula: =TODAY() - E2<30 then use the formatting desired. In the applies to fiels put the E:E – Scott Craner
It is working but only for Field E2. Not for the other fields. – Silve2611
It is not working. It is just always Coloring E2 and E1 I only want the cells to be colored where the dates are in range of 30 days – Silve2611

2 Answers

1
votes

enter image description here

I used the following formulas:

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

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.