Apply Conditional Formatting to Dates in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on June 30, 2023

This tutorial demonstrates how to apply conditional formatting to date values in Excel and Google Sheets.

 

conditional formatting dates date occurring last week 2

 

Apply Conditional Formatting to Dates

In Excel, you can apply different conditional formatting rules to dates, using built-in options and formulas. Let’s first see how to highlight (in red) dates occurring in the last week. For this example, assume today is 6/7/2021. So the past week is the date range: 5/31/2021–6/6/2021. Below is the list of dates in Column B.

 

conditional formatting dates initial data

 

  1. Select the range of dates (B2:B10), and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.

 

conditional formatting dates date occurring

 

  1. In the pop-up window, choose Last week from the drop-down menu and click OK. As you can see, there are other options in the list (Yesterday, Today, Tomorrow, etc.) as well.

 

conditional formatting dates date occurring last week

 

As a result, cells with dates within the last week are highlighted red (B7, B8, and B10).

 

conditional formatting dates date occurring last week 2

 

Highlight Dates in a Date Range

Now, to highlight dates less than 5 days ago (in this case, 6/3/2021 – 6/6/2021), use the AND Function and TODAY Function .

  1. Select the range of dates and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

highlight dates between date range

 

  1. In the New Formatting Rule window, (1) select Use a formula to determine which cells to format as the Rule Type and (2) enter the formula:
=AND(TODAY()-$B2>0, TODAY()-$B2<5)

Then (3) click Format.

 

highlight dates between date range 2

 

This formula goes cell-by-cell, checking whether the difference between today and the date in each cell is greater than 0 and less than 5. If both conditions apply, the result of the formula is TRUE, and the conditional formatting rule is applied. This means that the date in a green cell is in the last 5 days.

  1. In the Format Cells window, go to the Fill tab, select a color (green), and click OK.

 

highlight dates between date range 3

 

  1. This takes you back to the New Formatting Rule window, where you can see a Preview of the formatting and click OK to confirm.

 

highlight dates between date range 4

 

As a result, dates in the last 5 days (here, the current date is 6/7/2021) are highlighted in green (B3, B7, and B10).

 

highlight dates between date range final

 

Highlight Weekends

You can also use conditional formatting to highlight dates that fall on Saturdays and Sundays, using the WEEKDAY Function.

  1. Select the range with dates (B2:B10), and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

highlight dates between date range

 

  1. In the New Formatting Rule window, (1) select Use a formula to determine which cells to format for a Rule type and (2) enter the formula:
=WEEKDAY($B2,2)>5

Then (3) click Format.

 

highlight weekends excel

 

The WEEKDAY Function returns the number of the day in a week. The second parameter value 2, means that the week starts at Monday (1) and ends at Sunday (7). Therefore, for every cell in a range, you are checking if the weekday is greater than 5, which means that the date is on Saturday or Sunday. If that’s true, the conditional formatting rule is applied.

  1. In the Format Cells window, (1) go to the Fill tab, (2) select a color (green), and (3) click OK.

 

highlight dates between date range 3

 

  1. This takes you back to in the New Formatting Rule window, where you can see the preview of the formatting and click OK to confirm.

 

highlight weekends excel 2

 

Now, all dates occurring on weekends are highlighted in green (B3, B6, and B10).

 

highlight weekends excel final

 

Apply Conditional Formatting to Dates in Google Sheets

You can also use conditional formatting to highlight dates in Google Sheets. To highlight dates in the past week follow these steps:

  1. Select the range of dates (B2:B10), and in the Menu, go to Format > Conditional formatting.

 

google sheets conditional formatting dates

 

  1. On the right side of the window, (1) select Date is for Format rules, (2) choose In the past week, and (3) click Done. This leaves the default color, green, but if you want to change it, you can click on the Fill color icon.

 

google sheets conditional formatting dates 2

 

The result is almost the same as in Excel. Note that Google Sheets, the past week includes today.

 

google sheets conditional formatting dates 3

 

In addition to “past week,” there are other options that can be selected with this formatting rule.

 

google sheets conditional formatting dates options

 

Highlight Weekends in Google Sheets

As in Excel, you can use formulas in Google Sheets to create more complex rules for conditional formatting.

  1. Select the range of dates and in the Menu, go to Format > Conditional formatting.

 

google sheets conditional formatting dates

 

  1. In the rules window on the right, (1) select Custom formula is for Format rules and (2) enter the formula:
=WEEKDAY($B2,2)>5

Then (3) click Done.

 

google sheets highlight weekends

 

The formula works exactly the same as in Excel, returning 6 for Saturday and 7 for Sunday. As a result, weekends are highlighted in green.

 

google sheets highlight weekends 2

AI Formula Generator

Try for Free

See all How-To Articles