conditional formatting help...please!!!

[jever96]

I'm requesting help for conditional formatting. Here's the scenerio...
It seems relatively simple but... I'm tracking dates. I type a date (7-Jan-06) into a cell, depending on the date, it needs to be white, yellow or red.

1. Dates between 335-364 days from the date entered into the cell need to be yellow (fill).
2. Dates older than 365 days from the date entered into the cell need to be red (fill).

I'm new to excel and have been learning but this has stopped me in my tracks. Help!

 

[Scott Huish]

Hi, welcome to the board!

Assuming column A, change as necessary

Formula 1:

=(TODAY()-$A1>365)*($A1<>"")

Formula2:

=(TODAY()-$A1>334)*($A1<>"")

You also didn't say what you wanted to do if it was 365 days.
If you want that included in the first formula, change it to this:

=(TODAY()-$A1>364)*($A1<>"")

 

[jever96]

Help...thank you

Thank you very much...this works great. Thank you again!

 

[Peter_SSs]

jever96

Welcome to the Mr Excel board!

You say that you are new to Excel, so here is a little additional information that may be of use in this or future projects.

The TODAY() function is a 'volatile' function which calculates more often than the 'standard' Excel functions. If you use it a lot in a sheet (for example if you have a lot of cells that are using this Conditional Formatting which uses the formula twice in each cell) then you may find the performance of the sheet deteriorate markedly.

One way to overcome that would be to put the formula =TODAY() in a cell somewhere out of the road (maybe even in a hidden column) and then refer to that cell in your formulas.

For example, if =TODAY() was in cell Z1, then your Conditional Formatting formulas as suggested by HOTPEPPER would become:

=($Z$1-$A1>365)*($A1<>"")
and
=($Z$1-$A1>334)*($A1<>"")

This would mean that TODAY() was only calculating once on the sheet but your Conditional Formatting formulas would still produce the same results.