Changing font color to Red when 30 days passes

[John Fletcher]

Hello,

I would like to know if it's possible to change the color of the Date font once a specific amount of times has lapsed.. IE:

PA Due Date Column - indicates the date that a contract terminates - based on =TODAY() - is it possible to have the dates font turn red once thirty days passes the original date entered into the cell by the user?


Thanks in Advance

John

[Paul]

Hi John,

You can do this via Conditional Formatting.

For example, if your dates are in A1 through A10:

Select cells A1:A10, click Format -> Conditional Formatting
Choose 'Formula Is' and set the formula to:

=TODAY()-30>A1

Click the Format button to set the color/bold/etc. of the font you wish then click OK.

[John Fletcher]

Thank you for your reply... I am using Excel 2007 and I am looking for the Conditional Formating option.... can you advise as to where this feature may be?

Thanks,

John

[Paul]

John,

On the 'Home' ribbon bar, about two-thirds of the way to the right you should see the 'Conditional Formatting' icon. Click it and select 'New Rule...'

In the New Formatting Rule window, select 'Use a formula to determine which cells to format', then below type in the formula I gave you earlier.

Click the Format button, set your font/cell properties and click OK twice to return to the sheet. Your cells should now be formatted as hoped.

[Paul]

With Excel 2007 there are also some additional options at your fingertips by clicking the Conditional Formatting button.

You could do this same thing by:
1. Select cells A1:A10
2. Click Conditional Formatting button and select Highlight Cells Rules --> Less Than...
3. In the 'Format Cells that are less than' window, type the formula =TODAY()-30
4. Click the drop-down arrow on the right (should have some pre-defined color format selected) and pick 'Custom Format' at the bottom of the list. Set your format and click OK twice. Done!

[John Fletcher]

Thanks for the guidence.... I am wondering if I have explained this wrong... I will try it this way:

On the Spreadsheet - Column F - the entire column ic coded =TODAY() - reason for this is I am using =IF(OR(E3="",F3=""),"",DAYS360(E3,F3)) to calculate how many days old a file is in Column E and providing the answer in column F.... Now - The Column G indicates the date that the contract will terminate for the customer... Thus each date in Column G is defferent and not predicated on realtime =TODAY() understanding..... I want to use a formula to change the font colors of the dates (whatever they may be in column G) to red - once 30 days passes for each - when ever they are entered.... Does this make sence?

John