Way to make cell color sensitive to date?

[David4321]

Hello & thanks for looking,

What I want is to make date formatted cells change color based on a parameter related to the date. For example, turn to red 7 days before the date in the field, and back to green when the date has passed, or when it is changed (updated).

It's basicaly a payment reminder, so if this is not exactly possible, are there any other ways to do it? Maybe the cell turns red on a predetermined date each month, and back to green on another date?

Thanks.

 

[Worf]

Hi

You can use conditional formatting based on formula. Something like

=today() - A1 > 0 (just an example)

and format the cell fill color to change if the formula is TRUE.

 

[Ifrite]

You may use Conditional Formatting to deal with this.
1. Create a format rule based on "Use a formula to determine which cells to format"
2. Assuming you wanna Cell B1 (B1=today()) to change into red based on the days between A1 and B1, you shall input the formula =AND(A1-B1<=7,A1-B1>=0)
3. Format the cell if the condition above is true. Done

Now, if your B1 date is no more than 7 days away from the A1 date, the B1 cell will turn into red or whatever format you prefer.

 

[Matt Rogers]

Hi and welcome to the board.

Maybe try this:

<table valign="middle" colspan="2" style="font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; " border="1" cellpadding="2" cellspacing="0"><colgroup><col width="62,4pt"></colgroup><tbody><tr><td style="background-color:#92D050; " align="right">11/09/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/10/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/11/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/12/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/13/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/14/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/15/2011</td></tr><tr><td style="background-color:#FF0000; " align="right">11/16/2011</td></tr><tr><td align="right">11/17/2011</td></tr></tbody></table>
<table valign="middle" colspan="3" style="color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;" border="3" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#aaaaee"><td>Cell
</td><td>Formula
</td><td>Format</td></tr><tr><td>A1</td><td>1: =A1=TODAY()</td><td style="color:#keine; background-color:#92D050; ">abc</td></tr><tr><td>A1</td><td>2: =A1<TODAY()+8</td><td style="color:#keine; background-color:#FF0000; ">abc</td></tr></tbody></table>