Using conditional formatting to highlight dates in Excel....
Date functions in Excel make it is
possible to perform date calculations, like addition or subtraction, resulting
in automated or semi-automated worksheets. The NOW function, which calculates
values based on the current date and time, is a great example of this.
Taking this functionality a step further, when you mix date
functions with conditional formatting, you can create spreadsheets that display
date alerts automatically when a deadline is near or differentiates between
types of days, like weekends and weekdays.
The basics of conditional
formatting for dates
To find conditional formatting for dates, go to
Home > Conditional Formatting > Highlight
Cell Rules > A Date Occuring.
You can select the following date options, ranging from
yesterday to next month:
These 10 date options generate rules based on the current
date. If you need to create rules for other dates (e.g., greater than a month
from the current date), you can create your own new rule.
Below are step-by-step instructions for a few of my favorite
conditional formats for dates.
Highlighting weekends
When you design an automated
calendar you don’t need to color the weekends yourself. With the conditional
formatting tool, you can automatically change the colors of weekends by basing
the format on the WEEKDAY function. Assume that you have
the date table–a calendar without conditional formatting:
To change the color of the weekends, open the menu Conditional
Formatting > New Rule
In the next dialog box, select the menu Use a
formula to determine which cell to format.
In the text box Format values where this formula is true, enter
the following WEEKDAY formula to determine whether the cell is a Saturday
(6) or Sunday (7):
=WEEKDAY(B$5,2)>5
The parameter 2 means Saturday = 6 and Sunday = 7. This
parameter is very useful to test for weekends.
Note: In this case, you
must lock the reference of the row so that the conditional format will
work correctly in the other cells in this table.
Then, customize the format of your condition by clicking on
the Format button and you choose a fill color (orange in this
example).
Click OK, then open Conditional
Formatting> Manage Rules
Select This Worksheet to see the worksheet rules instead
of the default selection. In Applies to, change
the range that corresponds to your initial selection when creating your rules
to extend it to the whole column.
Now you will see a different
color for the weekends. Note: This
example shows the result in the Excel Web App.
Highlighting holidays
To enrich the previous workbook, you could also color-code
holidays. To do that, you need a column with the holidays you’d like to
highlight in your workbook (but not necessarily in the same sheet). In our
example, we have US public holidays in column AH (as related to the year
in the cell B2).
Again, open the menu Conditional
Formatting > New Rule. In this case, we use the formula COUNTIF in
order to count if the number of public holidays in the current month is greater
than 1.
=COUNTIF($AH$4:$AH$16,B$5)>1
Then, in the dialog box Manage Rules, select
the range B4:AF11. If you want to highlight the holidays over the weekends, you
move the public holiday rule to the top of the list.
This example in the Excel Web App below shows the result. Change
the value of the month and the year to see how the calendar has a different
format.
Highlighting delays
In case we want to change the color of cells based on our
approach on a date again, we will use conditional formatting to make it work
for us.
In the following example, we show:
- yellow
dates between 1 and 2 months
- orange
dates between 2 and 3 months
- purple
dates more than 3 months
We then construct three rules
conditional formatting using formula DATEDIF . Respectively
for the three cases the following formulas:
=DATEDIF($B2,$E$2,”m”)>0
=DATEDIF($B2,$E$2,”m”)>1
=DATEDIF($B2,$E$2,”m”)>2
In the Excel Web App below, try changing some dates to
experiment with the result.
Color scales
Rather than choose a different color set for each period in
our timeframe, we will work with the option of color scales to color our cells.
First, go into a new column (column E),
calculate the difference in number of days in a year again with the DATEDIF
formula and the parameter “yd”.
=DATEDIF($D2,TODAY(),”yd”)
Then choose the menu Conditional Formatting> New
Rule option Format all cells based on their value and choose the
following options:
- Scale = 3 colors
- Minimum = 0 red
- Midpoint = 10 yellow
- Maximum = 30 white
The result is a gradient color scale with nuances from
white to red through yellow. The closer to 0, the more red it will
have, the closer to 10 the more yellow, and the closer to 30 the more
white. In the Web App below, try changing some dates to experiment
with the result.
Excel
is Fun...
