0
0
Excelspreadsheet~10 mins

Conditional formatting with dates in Excel - Cell-by-Cell Formula Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

A list of tasks with their due dates in column B.

CellValue
A1Task
B1Due Date
A2Buy groceries
B22024-06-10
A3Pay bills
B32024-06-15
A4Doctor appointment
B42024-06-05
A5Meeting with team
B52024-06-12
Formula Trace
=B2<TODAY()
Step 1: B2
Step 2: TODAY()
Step 3: "2024-06-10" < "2024-06-08"
Cell Reference Map
    A               B
1 | Task            | Due Date  
2 | Buy groceries   | 2024-06-10
3 | Pay bills       | 2024-06-15
4 | Doctor appointment | 2024-06-05
5 | Meeting with team | 2024-06-12

Formula references cell B2 and uses TODAY() function.
The formula checks if the date in B2 is before today's date.
Result
    A                 B           C
1 | Task              | Due Date  | Highlight
2 | Buy groceries     | 2024-06-10| FALSE
3 | Pay bills         | 2024-06-15| FALSE
4 | Doctor appointment| 2024-06-05| TRUE
5 | Meeting with team | 2024-06-12| FALSE

Cells with TRUE would be highlighted by conditional formatting.
Only the task with a due date before today (June 8, 2024) is highlighted.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the formula =B2<TODAY() check?
AIf the due date in B2 is equal to today
BIf the due date in B2 is after today
CIf the due date in B2 is before today
DIf the due date in B2 is empty
Key Result
Conditional formatting formula compares a date cell to TODAY() to highlight past dates.