0
0
Google Sheetsspreadsheet~10 mins

Date-based formatting in Google Sheets - 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-20
A5Meeting with team
B52024-06-25
A6Submit report
B62024-06-30
Formula Trace
=B2<TODAY()
Step 1: B2
Step 2: TODAY()
Step 3: 2024-06-10 < 2024-06-18
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-20 |
5 | Meeting with team | 2024-06-25 |
6 | Submit report   | 2024-06-30 |

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  | Overdue? |
2 | Buy groceries   | 2024-06-10 | TRUE    |
3 | Pay bills       | 2024-06-15 | TRUE    |
4 | Doctor appointment | 2024-06-20 | FALSE   |
5 | Meeting with team | 2024-06-25 | FALSE   |
6 | Submit report   | 2024-06-30 | FALSE   |
Column C shows TRUE if the task is overdue (due date before today), FALSE otherwise.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the formula =B2<TODAY() check?
AIf the date in B2 is after today
BIf the date in B2 is equal to today
CIf the date in B2 is before today
DIf the date in B2 is empty
Key Result
Date-based formatting formulas compare a date cell with TODAY() to check if it is before or after the current date.