Conditional Formatting Based on Date in Another Cell – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on July 9, 2022

This tutorial will demonstrate how to highlight cells based on a date in another cell using Conditional Formatting in Excel and Google Sheets.

 

condition formatting date in cell master

 

Highlight Cells With Conditional Formatting

To highlight cells that contain a date greater than a date specified in another cell, use a simple formula within Conditional Formatting.

In the following example, you have a table of dates (B4:E12) and want to emphasize which dates are after August 1, 2020. One way to do that is to set another cell (here, G9) to 2020/08/01 and compare each date.

  1. Select the range where you want to highlight days.
  2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

conditional formatting date in cell menu

 

  1. Select (1) Use a formula to determine which cells to format, and (2) enter the formula:
=B4>$H$8

You need to lock the reference to cell H9 by making it absolute. You can do this by typing $ signs around the row and column indicators, or by pressing F4 on the keyboard.
(3) When done, click on Format…

 

conditional formatting date in cell formula

 

  1. Select your desired formatting. For this example, apply an orange fill.

 

conditional formatting date in cell formats

 

  1. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

conditional formatting date in cell rule manager

 

  1. Click Apply to apply the formatting to your selected range and click Close.

 

conditional formatting date in cell master final

 

Every cell in the range selected where the date in that cell is greater than the date in cell H8 will have its background color changed to orange.

Highlight Cells with Conditional Formatting in Google Sheets

The process to highlight cells based on the date entered in that cell in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format > Conditional Formatting.

 

google sheets conditional formatting date in cell menu

 

  1. The Apply to Range section is automatically filled in.

 

google sheets conditional formatting date in cell range

 

  1. From the Format Rules section, select Custom formula is.

 

google sheets conditional formatting dates in cell formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting date in cell format

 

  1. Click Done to apply the rule.

 

google sheets conditional formatting date in cell master final

 

The result is the same: Dates later than Aug 1 are highlighted in orange.

AI Formula Generator

Try for Free

See all How-To Articles