Conditional Formatting – Highlighting dates

With Date functions in Excel, it is possible to perform calculations like addition or subtraction and thus, produce automated or semi-automated worksheets (using the NOW function in particular). Date functions mix with conditional formatting, allow you to create spraedsheets displaying date alerts automatically when a deadline is near.

Basics of Conditional formatting with dates

Menu - Conditional Formatting - A Date Occuring

In the menu Home / Conditional formatting / Highlight Cells Rules > A Date Occurring..., you can create following rules :

Available options are:

• Yesterday
• Today
• Tomorrow
• In the last 7 days
• Last week
• This week
• Next week
• Last month
• This month
• Next month

All these options generate rules around the current date only which means you don't have an easy way to highlight cells where the gap is greater than one month. If you want to highlight cells greater or lower than a month from the current date, you will need to create your own rules.

Highlighting weekends

When you design an automated calendar you don't need to color weekends yourself after each update. With the conditional formatting tool, you can automatically change colors of weekends by basing the format on the WEEKDAY function.  Assume that you have the date table shown below:

Calendar without conditional formatting

To change the color of weekends, open the menu Conditional Formatting > New Rule

In the next dialog box, you select the menu Use a formula to determine which cell to format.

In the text box "Format values where this formula is true", you enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7).

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 in order to the conditional format works correctly in the other cells in this table.

Then, you customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example)

After pressing OK, open Conditional Formatting> Manage Rules

Select this option to visualize the worksheet  rules instead of the default selection. In the Applies to, change the range which corresponds to your initial selection when creating your rules to extend it to the whole column.

Change values for the month and the year in the following spreadsheet. You will see a different color for weekends.

Highlighting Public holidays

To enrich the previous workbook, you want also to color public holidays. To do that, you must have a column of public holidays in your country in a column in your workbook (but not necessarily in the same sheet.) In our example, we have public holidays of the USA in column AH (as related to the year in the cell B2.)

One more time, we will use a formula with conditional formatting. 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.

Then, in the dialog box "Manage rules" , you select the range B4:AF11. If you want to highlight holidays over weekends, you move public holidays rule to the top of the list.

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 than 3 months

We then construct three rules conditional formatting using formula DATEDIF . Respectively, for three cases you will have following formulas:

• =DATEDIF($B2,$E$2,"m")>0
• =DATEDIF($B2,$E$2,"m")>1
• =DATEDIF($B2,$E$2,"m")>2

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, we 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".

Then we choose the menu Conditional Formatting> New Rule option Format all cells based on their value and choose following options:

• Scale 3 colors
• Minimum, 0 red
• Median, 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.

Related posts