Date-based formatting in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Highlight dates within the next 7 days

You want to highlight cells in column A that contain dates within the next 7 days from today. Which custom formula should you use in conditional formatting?

A =A1>=TODAY()-7

B =AND(A1>TODAY(), A1<TODAY()+7)

C =AND(A1>=TODAY(), A1<=TODAY()+7)

D =A1<TODAY()+7

💡 Hint

Think about including today and the next 7 days exactly.

The formula =AND(A1>=TODAY(), A1<=TODAY()+7) checks if the date in A1 is today or later, but not more than 7 days ahead. This correctly highlights dates within the next 7 days including today.

❓ Function Choice

intermediate

Choose the function to check if a date is in the past

Which function correctly returns TRUE if the date in cell B2 is before today?

A =B2<TODAY()

B =ISBEFORE(B2, TODAY())

C =DATEVALUE(B2)<TODAY()

D =B2<=NOW()

💡 Hint

Compare the date directly to TODAY().

The formula =B2<TODAY() returns TRUE if B2 is a date before today. The function ISBEFORE does not exist in Google Sheets. DATEVALUE expects a text string, so if B2 is already a date, it is unnecessary. Using <=NOW() includes time and may cause unexpected results.

🎯 Scenario

advanced

Conditional formatting for overdue tasks

You have a list of tasks with due dates in column C. You want to highlight tasks that are overdue (due date before today) but only if the task status in column D is not "Completed". Which custom formula should you use for conditional formatting starting at row 2?

A =AND(C2<TODAY(), D2<>"Completed")

B =AND(C2<=TODAY(), D2!="Completed")

C =IF(C2<TODAY(), D2<>"Completed")

D =AND(C2<TODAY(), NOT(D2="Completed"))

💡 Hint

Use AND to combine conditions and exact comparison for status.

Option A uses AND(C2<TODAY(), D2<>"Completed") which correctly checks if the due date is before today and the status is not "Completed". Option A includes due dates equal to today, which is not overdue. Option A uses IF incorrectly inside conditional formatting. Option A is logically correct but more complex; however, it works but option A is simpler and preferred.

❓ data_analysis

advanced

Count dates in the current month

You have dates in column E. Which formula correctly counts how many dates fall in the current month?

A =COUNTIFS(E:E, ">="&EOMONTH(TODAY(),-1)+1, E:E, "<="&EOMONTH(TODAY(),0))

B =COUNTIFS(E:E, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1), E:E, "<"&DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1))

C =COUNTIF(E:E, "<"&EOMONTH(TODAY(),0))

D =COUNTIF(E:E, ">="&TODAY()-DAY(TODAY())+1)

💡 Hint

Use COUNTIFS with start and end dates of the current month.

Option B counts dates from the first day of the current month (inclusive) to the first day of the next month (exclusive), correctly counting all dates in the current month. Option B uses EOMONTH with +1 day but is more complex and less clear. Option B counts dates after the first day but no upper limit. Option B counts dates before the end of the month but no lower limit.

📊 Formula Result

expert

Result of complex date formula

What is the result of this formula if today is June 15, 2024, and cell F1 contains the date June 10, 2024? =IF(AND(F1=EOMONTH(TODAY(), -1)+1), "Last Month", "Other")

A "Last Month"

B "Today"

C Error

D "Other"

💡 Hint

Check if F1 is between the first day of the current month and yesterday.

Given TODAY() is June 15, 2024, EOMONTH(TODAY(), -1) returns May 31, 2024. Adding 1 gives June 1, 2024. The formula checks if F1 is before June 15 and on or after June 1. F1 is June 10, which fits, so the formula returns "Last Month".

Practice

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