Conditional formatting with dates in Excel - Step-by-Step Guide

Introduction

Conditional formatting with dates helps you highlight cells based on date values. This makes it easy to spot upcoming deadlines, past due dates, or dates within a specific range without scanning all the data.

When you want to highlight tasks that are due within the next week in your project tracker.

When you need to mark expired coupons or offers in a sales list.

When you want to color-code dates that fall on weekends or holidays.

When you want to quickly see which invoices are overdue in your accounting sheet.

When you want to highlight birthdays coming up this month in a contact list.

Steps

Step 1: Select the range of cells containing dates

- Excel worksheet

The cells you want to format are highlighted and ready for conditional formatting

Step 2: Click

- Home tab > Conditional Formatting button

A dropdown menu with formatting options appears

Step 3: Select

- Highlight Cells Rules > A Date Occurring...

A dialog box opens with date-based formatting options

Step 4: Choose

- The dropdown in the dialog box (e.g., Yesterday, Today, Tomorrow, Next 7 days)

The selected date condition is set for formatting

Step 5: Pick

- A formatting style from the presets or click Custom Format to choose font color, fill color, or border

The preview shows how the formatting will look

Step 6: Click

- OK button in the dialog box

Cells matching the date condition are highlighted with the chosen format

Before vs After

Before

Column A shows dates like 2024-06-01, 2024-06-10, 2024-06-15 with no color

After

Dates that are today or within the next 7 days are highlighted with a light green fill

Settings Reference

Rule Type

📍 Conditional Formatting > New Rule dialog

Choose how to specify the condition for formatting

Default: Format only cells that contain

Date Occurring Options

📍 Conditional Formatting > Highlight Cells Rules > A Date Occurring dialog

Select the date range condition to highlight

Default: Today

Format Style

📍 Conditional Formatting > Highlight Cells Rules > A Date Occurring dialog > Custom Format

Define how the highlighted cells will look

Default: Light red fill with dark red text

Common Mistakes

Selecting cells that do not contain dates

Conditional formatting with date rules only works correctly on cells formatted as dates

Ensure the selected cells are formatted as dates before applying conditional formatting

Choosing a wrong date condition like 'Yesterday' when you want to highlight future dates

The formatting will highlight past dates, not upcoming ones

Select the correct date condition such as 'Next 7 days' to highlight future dates

Not clicking OK after setting the rule

The formatting rule will not be applied until confirmed

Always click OK to apply the conditional formatting rule

Summary

Conditional formatting with dates highlights cells based on date conditions like today, next week, or last month.

It helps quickly identify important dates such as deadlines or expired items.

Make sure your cells are formatted as dates and choose the correct date condition for accurate highlighting.

Practice

1. What does conditional formatting with the formula =A1<TODAY() do in Excel?

easy

A. Highlights cells with dates equal to today

B. Highlights cells with dates before today

C. Highlights cells with dates after today

D. Highlights cells with empty dates

Solution

1. Step 1: Understand the formula =A1<TODAY()

   This formula checks if the date in cell A1 is less than today's date.

2. Step 2: Interpret the condition

   If the date is before today, the condition is TRUE and the formatting applies.

3. Final Answer:

   Highlights cells with dates before today -> Option B

4. Quick Check:

   Dates < TODAY() = Highlight past dates [OK]

Hint: Less than TODAY() means dates before today [OK]

Common Mistakes:

• Confusing less than with greater than
• Thinking it highlights today's date
• Using TODAY without parentheses

2. Which of these is the correct formula to highlight cells with dates equal to today in conditional formatting?

easy

A. =A1=TODAY()

B. =A1=TODAY

C. =A1==TODAY()

D. =A1=NOW()

Solution

1. Step 1: Identify correct function syntax

   TODAY() is a function and requires parentheses to work properly.

2. Step 2: Compare options

   =A1=TODAY() uses =A1=TODAY(), which correctly compares the cell date to today's date.

3. Final Answer:

   =A1=TODAY() -> Option A

4. Quick Check:

   Use TODAY() with parentheses for correct date comparison [OK]

Hint: TODAY() always needs parentheses [OK]

Common Mistakes:

• Omitting parentheses after TODAY
• Using double equals (==) which is invalid in Excel
• Using NOW() which includes time, not just date

3. Given the conditional formatting formula =AND(A1>=TODAY(), A1<=TODAY()+7), what dates will be highlighted?

medium

A. Dates from today up to 7 days ahead

B. Dates exactly 7 days ago

C. Dates in the past week

D. Dates after 7 days from today

Solution

1. Step 1: Analyze the formula conditions

   The formula checks if the date in A1 is greater than or equal to today and less than or equal to today plus 7 days.

2. Step 2: Understand the date range

   This means it highlights dates starting from today up to 7 days in the future.

3. Final Answer:

   Dates from today up to 7 days ahead -> Option A

4. Quick Check:

   AND(A1≥TODAY(), A1≤TODAY()+7) = Next 7 days [OK]

Hint: Use AND to set date range between today and 7 days ahead [OK]

Common Mistakes:

• Thinking it highlights past dates
• Ignoring the AND condition
• Confusing TODAY()+7 as 7 days ago

4. You want to highlight dates older than 30 days using conditional formatting. Which formula has an error?

medium

A. =A1<TODAY()-30

B. =A1<=TODAY()-30

C. =A1<TODAY()-30()

D. =A1>TODAY()-30

Solution

1. Step 1: Check each formula's syntax

   Options A, B, and C use correct syntax with TODAY() function and arithmetic.

2. Step 2: Identify the error in =A1<TODAY()-30()

   =A1<TODAY()-30() uses TODAY()-30() which is invalid because 30() is not a function and parentheses after 30 cause error.

3. Final Answer:

   =A1<TODAY()-30() -> Option C

4. Quick Check:

   Numbers like 30 should not have parentheses [OK]

Hint: Numbers don't get parentheses; only functions do [OK]

Common Mistakes:

• Adding parentheses after numbers
• Using greater than instead of less than for older dates
• Omitting parentheses after TODAY

5. You want to highlight all dates in column A that are weekends (Saturday or Sunday) using conditional formatting. Which formula should you use?

hard

A. =WEEKDAY(A1)=6

B. =AND(WEEKDAY(A1)=7, WEEKDAY(A1)=1)

C. =OR(WEEKDAY(A1)=6, WEEKDAY(A1)=5)

D. =OR(WEEKDAY(A1)=7, WEEKDAY(A1)=1)

Solution

1. Step 1: Understand WEEKDAY function

   WEEKDAY returns 1 for Sunday and 7 for Saturday by default.

2. Step 2: Choose correct logical test

   We want to highlight if the date is Saturday OR Sunday, so use OR with conditions WEEKDAY=7 or WEEKDAY=1.

3. Final Answer:

   =OR(WEEKDAY(A1)=7, WEEKDAY(A1)=1) -> Option D

4. Quick Check:

   Use OR for weekend days 1 (Sun) and 7 (Sat) [OK]

Hint: Use OR with WEEKDAY=1 or 7 for weekends [OK]

Common Mistakes:

• Using AND instead of OR
• Wrong weekday numbers for weekend
• Checking only one weekend day