Conditional formatting with dates in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Highlight dates older than 30 days

You want to highlight cells in column A that contain dates older than 30 days from today using conditional formatting. Which formula should you use?

A =TODAY()-A1>30

B =A1>30-TODAY()

C =A1<TODAY()-30

D =A1+30<TODAY()

💡 Hint

Think about how to compare the date in the cell to today's date minus 30 days.

The formula =A1<TODAY()-30 checks if the date in A1 is earlier than 30 days before today, which means it is older than 30 days.

❓ Function Choice

intermediate

Choose the right function to highlight weekends

You want to apply conditional formatting to highlight dates in column B that fall on weekends (Saturday or Sunday). Which formula correctly identifies weekends?

A =ISOWEEKNUM(B1)>5

B =WEEKDAY(B1,1)<6

C =DAY(B1)>5

D =WEEKDAY(B1,2)>5

💡 Hint

Remember that the WEEKDAY function returns a number representing the day of the week.

WEEKDAY(B1,2) returns 1 for Monday through 7 for Sunday. Days greater than 5 are Saturday (6) and Sunday (7), so =WEEKDAY(B1,2)>5 correctly identifies weekends.

🎯 Scenario

advanced

Highlight dates within the next 7 days

You want to highlight dates in column C that are coming up within the next 7 days, including today. Which conditional formatting formula will do this correctly?

A =AND(C1>=TODAY(), C1<=TODAY()+7)

B =C1>TODAY()+7

C =C1<TODAY()-7

D =OR(C1<TODAY(), C1>TODAY()+7)

💡 Hint

Use a formula that checks if the date is between today and 7 days from today.

The formula =AND(C1>=TODAY(), C1<=TODAY()+7) checks if the date is today or later but not more than 7 days ahead.

📊 Formula Result

advanced

Identify expired dates

You have a list of expiration dates in column D. You want to highlight all dates that have already passed (before today). What formula should you use in conditional formatting?

A =D1>TODAY()

B =D1<TODAY()

C =D1=TODAY()

D =D1>=TODAY()

💡 Hint

Think about dates that are earlier than today.

The formula =D1<TODAY() highlights dates before today, meaning expired dates.

❓ data_analysis

expert

Count highlighted dates using conditional formatting

You applied conditional formatting with the formula =E1>=TODAY() to highlight dates in column E that are today or in the future. How can you count how many dates are highlighted without manually counting?

A =COUNTIF(E:E, ">="&TODAY())

B =COUNT(E:E)-COUNTIF(E:E, ">="&TODAY())

C =SUMPRODUCT(--(E1:E100<TODAY()))

D =COUNTIF(E:E, "<"&TODAY())

💡 Hint

Use a function that counts cells meeting the same condition as the formatting formula.

=COUNTIF(E:E, ">="&TODAY()) counts all dates in column E that are today or later, matching the conditional formatting rule.