# Apply Conditional Formatting to Dates in Excel & Google Sheets

Written by

Reviewed by

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

## 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.

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

- 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.

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

### 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 .

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

- 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**.

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.

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

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

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 Weekends

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

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

- 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**.

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.

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

- 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.

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

## 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:

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

- 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.

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

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

### Highlight Weekends in Google Sheets

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

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

- 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**.

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.