Change cell color based on date

[Guest]

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 d>30 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

 

[Guest]

Conditional Formatting only allows three colors. You use four, so the easy
solution is to set one as a default, then if the dates don't fit the CF this
color will show.
A sueful alternative is to use the sheet's change event.
Place the following code in the sheet's code page....and you can get there
quickly by right-clicking th esheet tab & selecting code page.
The code is pretty easy to follow...

Option Explicit

Private Enum eColors
orange = 52479
red = 255
green = 65280
yellow = 65535
End Enum

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("MyDate").Address Then
Select Case Target.Value - Range("controlDate").Value
Case Is > 30: Target.Interior.Color = eColors.green
Case Is > 20: Target.Interior.Color = eColors.orange
Case Is > 10: Target.Interior.Color = eColors.yellow
Case Else: Target.Interior.Color = eColors.red
End Select
End If

End Sub

 

[Guest]

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.

 

[Guest]

Hi Patrick,

Thank you for the quick reply and code. I entered the code exactly as you
wrote in the Sheet 5 (Code) window, and in the code I replaced "My Date" with
a cell reference in which a user-specified date appears (cell M8 in this
example), and replaced "Control Date" with cell ref E1 in which the fomula
=NOW() is entered.

Then when I change the date in M8 on Sheet 5, the code window appears with
an error box stating: Compile error: Expected: Identifier, and in the
code, Enum is highlighted. Hopefully you know what this all means and can
please help.

If it helps, I can modify the user requirements so if duration is =<10, cell
color= no color, then there would be only 3 colors (one for >10 to =<20, one
for >20 to =<30, and one for >30).

Many cells on the worksheet contain user-specified dates, and the dates will
be changed by the user as tasks progress, so I assume that where you show "My
Date" in the code, I would enter the range of cells in which user-specified
dates appear. Then when any date cell is changed or updated by the user, the
code will automatically check all cells in the range and change colors
accordingly.

Thank you in advance for your help with this debugging - RRP333

 

[Guest]

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

 

[Myrna Larson]

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

 

[Myrna Larson]

PS: Another example of the behavior of DAYS360: the number of days from Feb
27, 2005 to March 1, 2005 is 4 rather than 2, since using DAYS360's view of
the calendar, Feb has 30 days.

 

[Guest]

Hi Myrna,

You champion! Thank you so much. Using the CFs you suggested does the
trick, especially in the order you suggested. My only modification is the
formula is today()-B4, since the user-specified dates are past dates not
future dates. Also, rather than "hard-coding" the threshold durations (10,
20, 30), the formula includes a cell ref so a user can easily change the
threshold durations at which he wants the colors to change. The worksheet
will include cells in which the user can enter the desired threshold
durations that are to apply to a column of dates, as the threshold durations
will vary from column to column (each column represents a different business
task or business group).

The only potential hassle I see is that each cell will require setting up
the CF. Currently there are about 3000 cells and the worksheet will grow to
over 10000 or more cells. Perhaps as the worksheet grows, I wonder if code
may be more appropriate rather than entering/maintaining all the CFs.

Your explanation of the algorithms behind DAYS 360 is also very good. I now
can apply that for other future opportunities.

Thank you for your quick reply and solution - RRP333

 

[Myrna Larson]

If you copy formatting from one cell to another, the conditional formatting is
copied, too. Just be sure that you use absolute references to the cells that
contain the threshold values.

As far as DAYS360 is concerned, there are other ways of calculating time
intervals; they are explained in Help for the financial functions that need to
convert days into a fraction of a year, i.e. PRICE, YIELD, YEARFRAC, etc.