Conditional formatting using today's date against another date

Question

I am trying to use conditional formatting to change the look of cells when certain conditions are true.  I need two different formats for one cell.  One is if a certain cell(D3) is less than 10 by a certain date(E3 which is 3/24/2011), then I have used the conditional format to change the look of the cell one way.  The other format would be if D3 is 10 or more by that same date (E3) then it would change to look another way.  I believe I have the formatting correct.  When I put the formulas in, they are not working correctly.  Here are some formulas if have used:  =AND(D3<10,TODAY()>"3/24/2011")  =AND(D3>=10,"TODAY()"<="3/24/2011")  Please tell me what I am doing wrong.  I know it has to be something simple, but I can't figure this out and I have a deadline to get this done!!!

Please help!!

Answer 1

In your equations for conditional formatting I would usethe following, on the assumption that E3 has a true date in it.

=AND($D$3<10,TODAY()>$E$3)

=AND($D$3>=10,TODAY()<=$E$3)

Excel dates are expressed as a number, so by comparing today to a text string (which is what happens when you use " marks) it will always be false.

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 2

Dates are not stored by Excel the way it displays them (just think of the memory requirement for all the possible ways each individual date could be displayed and then multiply that by the number of dates Excel can handle); rather, Excel stores dates as a floating point number (the whole number part represents the number of days offset from "date zero", 12/31/1899, and the decimal part is the fraction of a 24 hour day the time represents, 6:00am is 0.25 for example). So, you comparison is testing to see if a floating point number is "greater than" or "less than or equal to" a string value that happens to look like a date. Instead of using "3/24/2011" in your tests, use DATE(2011,3,24) instead

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.