Date-Based Conditional Formatting Practice Questions with Solutions

Introduction

Date-Based Conditional Formatting helps you quickly identify important dates in an Excel worksheet. It can highlight overdue dates, upcoming deadlines, recent dates, weekends, specific months, and dates that fall within a particular period. In this chapter, you will practice 10 different date-based Conditional Formatting problems using employee, project, payment, order, and attendance data. Date-Based Conditional Formatting Practice Questions with Solutions to help you understand the concepts.

Question 1: Highlight Overdue Payment Dates

Problem Statement

You have a list of customer payments. Highlight all payment dates that are before 1 September 2026.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Less Than

Enter:

01-09-2026

Choose a formatting style and click OK.

Expected Result

These dates should be highlighted:

• 15-08-2026
• 20-07-2026
• 28-08-2026

Concepts Covered

• Date comparison
• Less Than rule
• Overdue dates
• Date-based Conditional Formatting

Question 2: Highlight Upcoming Project Deadlines

Problem Statement

Highlight all project deadlines that are between 1 October 2026 and 31 October 2026.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → Between

Enter:

01-10-2026

and:

31-10-2026

Choose a formatting style and click OK.

Expected Result

These deadlines should be highlighted:

• 05-10-2026
• 18-10-2026
• 25-10-2026
• 28-10-2026

Concepts Covered

• Between rule
• Date ranges
• Project deadline tracking

Question 3: Highlight Today’s Date

Problem Statement

Identify the record whose date is today’s date using Conditional Formatting.

Excel Data

Excel Solution

Select B2:B6.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → A Date Occurring

Select:

Today

Choose a formatting style and click OK.

Expected Result

If today’s date is 30 September 2026, the cell containing 30-09-2026 will be highlighted.

Concepts Covered

• Today rule
• Dynamic date formatting
• Date-based identification

Question 4: Highlight Dates From the Previous Month

Problem Statement

Highlight dates that belong to the previous month.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → A Date Occurring

Select:

Last Month

Choose a formatting style and click OK.

Expected Result

If the current month is September 2026, the August dates should be highlighted:

• 12-08-2026
• 25-08-2026

Concepts Covered

• Dynamic date rules
• Last Month
• Date-based filtering

Question 5: Highlight Dates From the Next Month

Problem Statement

Highlight dates that belong to the next month.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → Highlight Cells Rules → A Date Occurring

Select:

Next Month

Choose a formatting style.

Expected Result

If the current month is September 2026, these October dates should be highlighted:

• 05-10-2026
• 12-10-2026
• 25-10-2026
• 30-10-2026

Concepts Covered

• Next Month rule
• Dynamic date formatting
• Future dates

Question 6: Highlight Weekend Dates

Problem Statement

Highlight dates that fall on Saturday or Sunday.

Excel Data

Excel Solution

Select A2:A8.

Go to:

Home → Conditional Formatting → New Rule

Choose:

Use a formula to determine which cells to format

Enter:

=WEEKDAY(A2,2)>5

Choose the required formatting and click OK.

Expected Result

The following dates should be highlighted:

• 26-09-2026
• 27-09-2026
• 03-10-2026
• 04-10-2026

Concepts Covered

• Formula-based Conditional Formatting
• WEEKDAY
• Weekend identification
• Date formulas

Question 7: Highlight Employee Birthdays in the Current Month

Problem Statement

Highlight employees whose birthdays fall in the current month, regardless of the year.

Excel Data

Excel Solution

Assuming the current month is September, select B2:B7.

Go to:

Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format

Enter:

=MONTH(B2)=MONTH(TODAY())

Choose a formatting style and click OK.

Expected Result

If the current month is September, these birthdays should be highlighted:

• 15-09-1998
• 08-09-2000
• 30-09-1996

Concepts Covered

• MONTH
• TODAY
• Formula-based Conditional Formatting
• Recurring annual dates

Question 8: Highlight Expired Memberships

Problem Statement

Highlight memberships whose expiry date has already passed.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format

Enter:

=B2<TODAY()

Choose a formatting style and click OK.

Expected Result

Any expiry date earlier than today’s date will automatically be highlighted.

Concepts Covered

• TODAY
• Date comparison
• Expired-date detection
• Dynamic Conditional Formatting

Question 9: Highlight Dates Within the Next 7 Days

Problem Statement

Highlight deadlines that occur within the next 7 days.

Excel Data

Excel Solution

Select B2:B7.

Go to:

Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format

Enter:

=AND(B2>=TODAY(),B2<=TODAY()+7)

Choose a formatting style and click OK.

Expected Result

Excel will automatically highlight dates from today through the next seven days.

Concepts Covered

• TODAY
• AND
• Date ranges
• Dynamic deadline tracking

Question 10: Create a Complete Project Deadline Tracker

Problem Statement

Create a practical project deadline tracker using Conditional Formatting.

Apply these rules:

1. Overdue projects → highlight dates before today.
2. Projects due within 7 days → apply another format.
3. Future projects beyond 7 days → apply a third format.

Excel Data

Excel Solution

Select B2:B8.

Rule 1: Overdue

Create a New Rule using:

=B2<TODAY()

Apply the required formatting.

Rule 2: Due Within 7 Days

Create another rule:

=AND(B2>=TODAY(),B2<=TODAY()+7)

Apply a different formatting style.

Rule 3: More Than 7 Days Away

Create another rule:

=B2>TODAY()+7

Apply a third formatting style.

Expected Result

The deadline column should automatically classify dates into:

• Overdue
• Due within 7 days
• Future deadline

The result will automatically change as the current date changes.

Concepts Covered

• Multiple date-based rules
• TODAY
• AND
• Formula-based Conditional Formatting
• Deadline tracking
• Dynamic project management

Key Takeaways

• Excel has built-in date rules such as Today, Yesterday, Tomorrow, Last Week, This Week, Next Week, Last Month, This Month, and Next Month.
• The Less Than rule can identify dates before a particular date.
• The Between rule can highlight dates inside a specific period.
• TODAY() is useful when Conditional Formatting needs to change automatically with the current date.
• WEEKDAY() can be used to identify weekends.
• MONTH() can help identify recurring monthly events such as birthdays.
• AND() can combine multiple date conditions.
• Formula-based Conditional Formatting is useful for dynamic deadline and expiry tracking.
• Date-based rules are useful for projects, payments, memberships, employee records, appointments, and task management.
• A well-designed date tracker can automatically identify overdue and upcoming records without manually checking each date.

FAQs

1. What is Date-Based Conditional Formatting in Excel?

Date-Based Conditional Formatting automatically changes the appearance of cells according to date conditions.

For example, you can highlight overdue dates or dates occurring next month.

2. How do I highlight today’s date in Excel?

Select the date range and go to:

Home → Conditional Formatting → Highlight Cells Rules → A Date Occurring → Today

Excel will highlight cells containing today’s date.

3. How can I highlight overdue dates?

You can create a formula-based rule:

=B2<TODAY()

This highlights dates that are earlier than today’s date.

4. How can I highlight dates coming in the next 7 days?

Use:

=AND(B2>=TODAY(),B2<=TODAY()+7)

This checks whether the date falls between today and seven days from today.

5. Can Excel automatically identify weekends?

Yes. You can use WEEKDAY() with Conditional Formatting.

For example:

=WEEKDAY(A2,2)>5

This identifies Saturday and Sunday.

6. Can I highlight birthdays occurring this month?

Yes. You can use:

=MONTH(B2)=MONTH(TODAY())

This compares the month of the birthday with the current month.

7. Does TODAY() update automatically?

Yes. TODAY() returns the current date and updates when Excel recalculates the workbook.

8. Can I highlight dates from the previous or next month?

Yes. Excel’s A Date Occurring rule includes options such as Last Month and Next Month.

9. Can I use multiple date rules on the same range?

Yes. For example, you can create separate rules for overdue dates, dates due within seven days, and future dates.

10. Where can I manage date-based Conditional Formatting rules?

Go to:

Home → Conditional Formatting → Manage Rules

You can view, edit, delete, or change the priority of the rules applied to your worksheet.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar