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. Click "Conditional formatting" button.
  4. Click on "Highlight Cells Rules".
  5. Click "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. Click 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. Click the "Conditional Formatting" button.
  3. Click "New Rule.." to open a dialog box.
  4. Click "Use a formula to determine which cells to format".
  5. Type the formula. (See below which formula to use).
  6. Click "Format..." button and choose a formatting.
  7. Click 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