conditional formatting for dates

[basil.licop]

Hi All,

I need some help with conditional formatting for dates.

Heres my criteria for conditional formatting:
1. if date is within 60 days - green color shading
2. if date > 60 days from today but < 120 days - yellow color shading
3. if date > 120 days from today - red color shading

seems simple enough but i have trouble figuring it out.. heres what i have to begin with & im sure I got it wrong.. help!

=IF(A2-TODAY()<=60,,) for #1

 

[Nogslaw]

Assuming that the dates in the range are future dates and not past dates, you were on the right track:

=A2-Today()<60
=A2-Today()<120
=A2-Today()>=120

Note that you need to result in a True/False argument for the Condition, so you could use =IF(A2-Today()<60,"True", "False"), but it is unnecessary to do so. The abbreviated statements above are sufficient.

 

[basil.licop]

Thanks but it still doesnt work... it seems to find everything true as per the first rule(green)

 

[Nogslaw]

I'm afraid I'm at a bit of a loss. It works correctly for me.

Lets see if we can figure out where the problem may be coming from

1. What is the range of data you are trying to add the conditional formatting to?
2. Did you accidentally make absolute references (like $A$2) instead of relative references(A2) in the conditions?
3. Are the cells containing the dates formatted as dates?
4. Click on a single cell in the range (other than A2) and go to Format>Conditional Formatting. What is the cell's address, the cell's value, and what does the condition say?

 

[basil.licop]

Here are my answers:

1. What is the range of data you are trying to add the conditional formatting to?
- column A

2. Did you accidentally make absolute references (like $A$2) instead of relative references(A2) in the conditions?
-no i didnt, i just put these rules under conditional formatting:
=IF(A1-TODAY()<60,"True","False") -> also tried A1-TODAY()<60 but same
=IF(A1-TODAY()<120,"True","False")
=IF(A1-TODAY()>120,"True","False")

3. Are the cells containing the dates formatted as dates?
- yes they are:
<table x:str="" style="border-collapse: collapse; width: 53pt;" border="0" cellpadding="0" cellspacing="0" width="71"><col style="width: 53pt;" width="71"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39979" align="right" height="17" width="71">6/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39887" align="right" height="17">3/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39859" align="right" height="17">2/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39797" align="right" height="17">12/15/2008</td> </tr> </tbody></table>

4. Click on a single cell in the range (other than A2) and go to Format>Conditional Formatting. What is the cell's address, the cell's value, and what does the condition say?

A1 has =IF(A2-TODAY()<60,"True","False")
A2 has =IF(A2-TODAY()<60,"True","False")
A3 has =IF(A3-TODAY()<60,"True","False")
..

 

[basil.licop]

Oh nevermind. I got the problem solved.

I got the equation wrong. the previous results to all negative results which always gives a value <60.

I rewrote the equation to TODAY()-A2 instead of the other way around and now it works fine.

Thanks anyway! Youve been really helpful!

 

[Nogslaw]

Okay, I see a couple of things:

Here are my answers:



3. Are the cells containing the dates formatted as dates?
- yes they are:
<table x:str="" style="border-collapse: collapse; width: 53pt;" border="0" cellpadding="0" cellspacing="0" width="71"><col style="width: 53pt;" width="71"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39979" align="right" height="17" width="71">6/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39887" align="right" height="17">3/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39859" align="right" height="17">2/15/2009</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="background: lime none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" x:num="39797" align="right" height="17">12/15/2008</td> </tr> </tbody></table>

Okay, these are past dates, not future dates, so if we subtract today's date from them it ends up as a negative number which is less than +60. (6/15/09 - 6/16/09 = -1). To modify for past dates, change to =TODAY()-A2<60. To measure 60 days before OR 60 days after, include an absolute value function like this: =ABS(TODAY()-A2)<60

4. Click on a single cell in the range (other than A2) and go to Format>Conditional Formatting. What is the cell's address, the cell's value, and what does the condition say?

A1 has =IF(A2-TODAY()<60,"True","False")
A2 has =IF(A2-TODAY()<60,"True","False")
A3 has =IF(A3-TODAY()<60,"True","False")
..

We need to modify the formatting so that it refers to the cell in question:

A1 should read =A1-TODAY()<60, etc
A2 should read =A2-TODAY()<60, etc
A3 should read =A3-TODAY()<60, etc

This can be done by highlighting the entire range, starting with the top-most, left-most cell. When the entire range is highlighted, go to your conditional formatting and enter the formulas and reference the top-most, left-most cell. So, if the Range A2:A20 was going to be formatted, you would select A2:A20 (starting with A2 and dragging down to A20) and for the format condition you would enter =A2-Today()<60. The formatting will automatically adjust the relative reference (A2) for the other cells.

 

[basil.licop]

I got it figured out

Oh nevermind. I got the problem solved.

I got the equation wrong. the previous results to all negative results which always gives a value <60.

I rewrote the equation to =IF(TODAY()-A2<60,"True","False") instead of the other way around and now it works fine.

Thanks anyway! Youve been really helpful!

 

[djm67]

Can anyone help an absolute novice, I can conditionally format dates using the today feature, but...............

I have dates in a column (past dates), I need the individual cell to change colour after 1730 days have passed from the date in each cell.

So the format is from the date in each cell, not today.

 

[Nogslaw]

For A date in cell A1, use the following:

Condition1:
Formula Is:  =A1+1730 < TODAY()