Bird
Raised Fist0
Excelspreadsheet~15 mins

Conditional formatting with dates in Excel - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 8 steps
Sample Data
Order IDCustomerDue Date
1001Smith Co.2024-06-10
1002Jones LLC2024-06-15
1003Green Inc.2024-06-20
1004Blue Corp.2024-06-05
1005Red Ltd.2024-06-12
1006Yellow Co.2024-06-17
1007Orange LLC2024-06-08
1008Purple 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.

Practice

(1/5)
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
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard