Conditional formatting with dates in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales coordinator at a retail company.

📋 Request: Your manager wants to quickly see which sales orders are overdue, which are due today, and which are upcoming in the next 7 days.

📊 Data: You have a list of sales orders with their order IDs, customer names, and delivery due dates.

🎯 Deliverable: Create a spreadsheet where the due dates are color-coded: red for overdue, yellow for due today, and green for due in the next 7 days.

Sample Data

Order ID	Customer	Due Date
1001	Smith Co.	2024-06-10
1002	Jones LLC	2024-06-15
1003	Green Inc.	2024-06-20
1004	Blue Corp.	2024-06-05
1005	Red Ltd.	2024-06-12
1006	Yellow Co.	2024-06-17
1007	Orange LLC	2024-06-08
1008	Purple Inc.	2024-06-14

1

Step 1: Select the range of due dates in the 'Due Date' column (cells C2 to C9).

Expected Result

Cells C2:C9 are selected for formatting.

2

Step 2: Open Conditional Formatting and create a new rule for overdue dates (dates before today). Use the formula: =C2<TODAY()

Formula: =C2<TODAY()

Expected Result

Cells with due dates before today will be highlighted.

3

Step 3: Set the format for overdue dates to fill the cell with red color.

Format: Fill color red

Expected Result

Overdue due dates are shown with red background.

4

Step 4: Create a new conditional formatting rule for due today dates. Use the formula: =C2=TODAY()

Formula: =C2=TODAY()

Expected Result

Cells with due dates equal to today will be highlighted.

5

Step 5: Set the format for due today dates to fill the cell with yellow color.

Format: Fill color yellow

Expected Result

Due today dates are shown with yellow background.

6

Step 6: Create a new conditional formatting rule for upcoming dates within the next 7 days. Use the formula: =AND(C2>TODAY(), C2<=TODAY()+7)

Formula: =AND(C2>TODAY(), C2<=TODAY()+7)

Expected Result

Cells with due dates in the next 7 days will be highlighted.

7

Step 7: Set the format for upcoming due dates to fill the cell with green color.

Format: Fill color green

Expected Result

Upcoming due dates are shown with green background.

8

Step 8: Check that the conditional formatting rules apply correctly to all due dates in the range.

Expected Result

Overdue dates are red, due today dates are yellow, upcoming dates within 7 days are green, and other dates have no fill.

Final Result

RED

↓

RED

↓

GREEN

↓

GREEN

↓

GREEN

↓

GREEN

↓

NO FILL

↓

NO FILL

✓Orders with due dates before today are clearly marked in red as overdue.

✓Orders due today are highlighted in yellow for immediate attention.

✓Orders due within the next 7 days are marked in green to show upcoming deadlines.

✓Orders beyond 7 days have no special formatting.

Bonus Challenge

Add a conditional formatting rule to highlight weekends (Saturday and Sunday) in the due dates with a light blue fill.

Show Hint

Use the formula =WEEKDAY(C2,2)>5 to detect weekends.