Bird
Raised Fist0
Google Sheetsspreadsheet~5 mins

Date-based formatting in Google Sheets - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What is date-based formatting in Google Sheets?
Date-based formatting changes the look of cells depending on the date values they contain. For example, you can color cells red if the date is past today.
Click to reveal answer
beginner
Which formula would you use to highlight dates before today?
Use the formula =A1<TODAY() in conditional formatting to highlight dates before today.
Click to reveal answer
beginner
How do you apply conditional formatting based on dates in Google Sheets?
Select the cells, go to Format > Conditional formatting, choose 'Custom formula is', enter a date formula like =A1>=TODAY(), then pick a format style.
Click to reveal answer
intermediate
What does the formula =AND(A1>=TODAY(), A1<=TODAY()+7) do in date-based formatting?
It highlights dates in cell A1 that are within the next 7 days, including today.
Click to reveal answer
beginner
Why use TODAY() instead of a fixed date in date-based formatting?
TODAY() updates automatically every day, so your formatting changes as time passes without needing manual updates.
Click to reveal answer
Which formula highlights dates that are exactly today?
A=A1=TODAY()
B=A1<TODAY()
C=A1>TODAY()
D=A1<=TODAY()
How do you open the conditional formatting menu in Google Sheets?
ATools > Conditional formatting
BData > Conditional formatting
CFormat > Conditional formatting
DInsert > Conditional formatting
What does the formula =A1>TODAY()+30 highlight?
ADates within the next 30 days
BDates in the past 30 days
CDates exactly 30 days ago
DDates more than 30 days in the future
Which function returns the current date in Google Sheets?
ANOW()
BTODAY()
CDATE()
DCURRENTDATE()
To highlight past due dates, which formula is correct?
A=A1<TODAY()
B=A1>=TODAY()
C=A1=TODAY()
D=A1>TODAY()
Explain how to set up conditional formatting to highlight dates within the next week.
Think about how to check if a date is between today and 7 days from today.
You got /6 concepts.
    Why is using the TODAY() function helpful in date-based formatting?
    Consider how dates change every day and how formulas can adjust automatically.
    You got /4 concepts.

      Practice

      (1/5)
      1. What does date-based formatting in Google Sheets primarily help you do?
      easy
      A. Highlight cells based on date conditions
      B. Sort dates in ascending order
      C. Convert text to dates automatically
      D. Calculate the difference between two dates

      Solution

      1. Step 1: Understand the purpose of date-based formatting

        Date-based formatting changes how cells look based on date rules, like coloring dates before or after today.

      2. Step 2: Compare with other options

        Sorting, converting, and calculating dates are different features, not formatting.

      3. Final Answer:

        Highlight cells based on date conditions -> Option A

      4. Quick Check:

        Date-based formatting = Highlight cells by date [OK]
      Hint: Date formatting changes cell look based on dates [OK]
      Common Mistakes:
      • Confusing formatting with sorting
      • Thinking it converts text to dates
      • Assuming it calculates date differences
      2. Which formula is correct to use in conditional formatting to highlight dates before today?
      easy
      A. =DATE()
      B. =TODAY()
      C. =A1
      D. =A1>NOW()

      Solution

      1. Step 1: Identify the correct comparison for dates before today

        To highlight dates before today, the cell date (A1) must be less than TODAY(), so formula is =A1

      2. Step 2: Check other options for correctness

        A uses DATE() incorrectly without arguments; B (=TODAY()

      3. Final Answer:

        =A1<TODAY() -> Option C

      4. Quick Check:

        Use =A1<TODAY() to highlight past dates [OK]
      Hint: Use =A1
      Common Mistakes:
      • Using NOW() instead of TODAY()
      • Reversing comparison signs
      • Using DATE() without arguments
      3. Given the formula =A2>=TODAY() in conditional formatting, what happens if A2 contains the date 2024-06-01 and today is 2024-05-30?
      medium
      A. The cell is not highlighted because 2024-06-01 is before today
      B. The cell is highlighted because 2024-06-01 is after today
      C. The formula causes an error
      D. The cell is highlighted only if time is included

      Solution

      1. Step 1: Compare the date in A2 with today's date

        A2 has 2024-06-01, which is after 2024-05-30 (today).

      2. Step 2: Evaluate the formula condition

        The formula checks if A2 is greater or equal to TODAY(), which is true, so the cell is highlighted.

      3. Final Answer:

        The cell is highlighted because 2024-06-01 is after today -> Option B

      4. Quick Check:

        =A2>=TODAY() is TRUE for future dates [OK]
      Hint: Dates after or on today make =A2>=TODAY() TRUE [OK]
      Common Mistakes:
      • Confusing before and after dates
      • Thinking formula causes error
      • Assuming time affects date comparison here
      4. You want to highlight dates that are exactly 7 days from today using conditional formatting. Which formula has an error?
      medium
      A. =A1=TODAY()+"7 days"
      B. =A1=TODAY()-7
      C. =A1=TODAY()+7
      D. =A1=EDATE(TODAY(),7)

      Solution

      1. Step 1: Understand how to add days to TODAY()

        Adding 7 as a number to TODAY() works to get a date 7 days ahead.

      2. Step 2: Identify the error in =A1=TODAY()+"7 days"

        =A1=TODAY()+"7 days" adds a string "7 days" instead of a number, causing a formula error.

      3. Final Answer:

        =A1=TODAY()+"7 days" -> Option A

      4. Quick Check:

        Add numbers, not strings, to TODAY() [OK]
      Hint: Add numbers, not text, to TODAY() for date math [OK]
      Common Mistakes:
      • Adding text instead of numbers
      • Subtracting instead of adding days
      • Using wrong operators
      5. You want to highlight all dates in column A that are within the next 3 days including today. Which conditional formatting custom formula should you use?
      hard
      A. =A1>TODAY()+3
      B. =OR(A1>=TODAY(), A1<=TODAY()+3)
      C. =A1
      D. =AND(A1>=TODAY(), A1<=TODAY()+3)

      Solution

      1. Step 1: Define the date range condition

        We want dates from today up to 3 days ahead, so A1 must be >= TODAY() and <= TODAY()+3.

      2. Step 2: Choose the correct logical function

        AND ensures both conditions are true simultaneously, so use =AND(A1>=TODAY(), A1<=TODAY()+3).

      3. Final Answer:

        =AND(A1>=TODAY(), A1<=TODAY()+3) -> Option D

      4. Quick Check:

        Use AND for date range between today and 3 days ahead [OK]
      Hint: Use AND to check date is between two dates [OK]
      Common Mistakes:
      • Using OR instead of AND
      • Checking only one side of the range
      • Using > or < incorrectly