Author: Oscar Cronquist Article last updated on October 30, 2018

Excel has a built-in feature that allows you to highlight dates if a given condition is met.The date conditions you can choose from are:

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

How to apply Conditional Formatting

  1. Select cell range containing dates.
  2. Go to tab "Home" on the ribbon if you are not already there.
  3. Press with left mouse button on "Conditional formatting" button.
  4. Press with mouse on "Highlight Cells Rules".
  5. Press with left mouse button on "A Date Occuring..."
  6. A dialog box appears that lets you specify the date condition and the formatting.
  7. Pick a prebuilt formatting or use custom format to create a new one.
    • Light red Fill with dark red text
    • Yellow fill with dark yellow text
    • Green Fill with dark green text
    • Light red fill
    • Red text
    • Red border
    • Custom format...
  8. Press with left mouse button on OK button.

Highlight rows/records

You need to use a formula instead of the prebuilt ones in order to highlight the entire row if date meets the condition.

  1. Go to tab "Home" on the ribbon.
  2. Press with left mouse button on the "Conditional Formatting" button.
  3. Press with left mouse button on "New Rule.." to open a dialog box.
  4. Press with left mouse button on "Use a formula to determine which cells to format".
  5. Type the formula. (See below which formula to use).
  6. Press with left mouse button on "Format..." button and choose a formatting.
  7. Press with left mouse button on OK button twice.

Highlight row if the date is yesterday

The following formula highlights all cells on the same row if the date in column D is yesterday:

=$D3=(TODAY()-1)

The $ (dollar sign) makes the cell reference absolute meaning it locks the column thus highlighting all cells on the same row if the date matches the condition.

Highlight row if the date is today

=$D3=TODAY()

Highlight row if the date is tomorrow

=$D3=(TODAY()+1)

Highlight row if the date is in the last 7 days

=$D3>=(TODAY()+7)

Highlight row if the date is in last week

=WEEKNUM($D3,1)=(WEEKNUM(TODAY(),1)-1)

Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.

Highlight row if the date is in this week

=WEEKNUM($D3,1)=WEEKNUM(TODAY(),1)

Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.

Highlight row if the date is in next week

=WEEKNUM($D3,1)=(WEEKNUM(TODAY(),1)+1)

Change the second argument in WEEKNUM function if the week doesn't begin with Sunday.

Highlight row if the date is in last month

=TEXT($D3, "YYYY-MM")=TEXT(DATE(YEAR(TODAY()), MONTH(TODAY())-1, DAY(TODAY())), "YYYY-MM")

Highlight row if the date is in this month

=TEXT($D3, "YYYY-MM")=TEXT(DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())), "YYYY-MM")

Highlight row if the date is in next month

=TEXT($D3, "YYYY-MM")=TEXT(DATE(YEAR(TODAY()), MONTH(TODAY())+1, DAY(TODAY())), "YYYY-MM")

Related articles

test
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]