I think you've been given bad advice. As you suspect, you don't need an extra
column for this, you can use a formula in the conditional formatting, AND you
definitely don't want to use the DAYS360 function to calculate the difference
between two dates. You get the number of days between 2 dates by simple
subtration.

The DAYS360 function is used by bond traders to get around the problems
introduced by months of different lengths. For example, it would tell you that
the difference between Jan 15 and Feb 15 is 30 days, and the difference
between Feb 15 and March 15 is also 30 days. I assume, since you are
interested in differences of 10 and 20 days, that you want actual differences,
which are 31 and 28 days, respectively.

Assuming the date in B4 is a future date. You need 3 conditional formatting
formulas, applied in this order

=B4-TODAY()30
=B4-TODAY()20
=B4-TODAY()10


On Tue, 1 Mar 2005 18:07:04 -0800, RRP333
wrote:

Hi Moo,

Thank you for quick reply. Your solution is nearly what is required. The
CF you suggested requires an additional cell (B5) to display the duration
(B3-B4) and it changes the color of cell B5, but I need the color of B4 (the
user-specified date) to change color. There are many cells on the worksheet
in which a user has to enter a date, so if possible it would be preferable to
not have to add additional cells to display the duration. If a formula is
used in the CF, will this help?

Thank you for your help and quick response - RRP333

"Moo" wrote:

Hi.

To do the following, set up the following example:

In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy)
In cell B4, type: 16/01/2005 (this is the user defined date)
In cell B5, type: =DAYS360(B4,B3)
Now set cell B5's colour to Green (select the cell, right click, Cell
Properties, Pattern, and select the Green colour)
Now, while still on B5, select Format Conditional Formatting.
First row should read "Cell Value Is", "less than or equal to", then type
10.
Click Format, and set the pattern to RED.
Now select "Add"
Second row should read "Cell Value Is", "less than or equal to", then type
20.
Click Format, and set the pattern to ORANGE.
Now select "Add"
Second row should read "Cell Value Is", "less than or equal to", then type
30.
Click Format, and set the pattern to YELLOW.

This will work, as it excludes the need to look for a 4th condition (a
limit
in Excel Conditional Formatting). If you wanted RED to be the default
colour, change all "less than or equal to" statements to "greater than".
The
NOW() statement prevents you needing to type in todays date.


"RRP333" wrote:

Dear Experts,

I would like my users to be able to input a date in a cell, then Excel
automatically calculates the elapsed duration in days from the inputted
date
to today's date, and changes the color of the cell to one of four colors,
based on the 3 user-specified durations that correspond to the three
colors.
So if the user-specified durations are 10 days (if duration d =< 10, cell
color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d
=<30,
cell color = yellow, and if d30 cell color = green), the cell color will
automatically change based on today's date. Ideally, the user-specified
thresholds can be different for each cell, or at least for each column.

Can this be done with conditional formatting or is code required? I'm
new
at both so any help would be greatly appreciated!

Thank you, RRP333