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".