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.

Practice

1. What does conditional formatting with the formula =A1<TODAY() do in Excel?

easy

A. Highlights cells with dates equal to today

B. Highlights cells with dates before today

C. Highlights cells with dates after today

D. Highlights cells with empty dates

Solution

1. Step 1: Understand the formula =A1<TODAY()

   This formula checks if the date in cell A1 is less than today's date.

2. Step 2: Interpret the condition

   If the date is before today, the condition is TRUE and the formatting applies.

3. Final Answer:

   Highlights cells with dates before today -> Option B

4. Quick Check:

   Dates < TODAY() = Highlight past dates [OK]

Hint: Less than TODAY() means dates before today [OK]

Common Mistakes:

• Confusing less than with greater than
• Thinking it highlights today's date
• Using TODAY without parentheses

2. Which of these is the correct formula to highlight cells with dates equal to today in conditional formatting?

easy

A. =A1=TODAY()

B. =A1=TODAY

C. =A1==TODAY()

D. =A1=NOW()

Solution

1. Step 1: Identify correct function syntax

   TODAY() is a function and requires parentheses to work properly.

2. Step 2: Compare options

   =A1=TODAY() uses =A1=TODAY(), which correctly compares the cell date to today's date.

3. Final Answer:

   =A1=TODAY() -> Option A

4. Quick Check:

   Use TODAY() with parentheses for correct date comparison [OK]

Hint: TODAY() always needs parentheses [OK]

Common Mistakes:

• Omitting parentheses after TODAY
• Using double equals (==) which is invalid in Excel
• Using NOW() which includes time, not just date

3. Given the conditional formatting formula =AND(A1>=TODAY(), A1<=TODAY()+7), what dates will be highlighted?

medium

A. Dates from today up to 7 days ahead

B. Dates exactly 7 days ago

C. Dates in the past week

D. Dates after 7 days from today

Solution

1. Step 1: Analyze the formula conditions

   The formula checks if the date in A1 is greater than or equal to today and less than or equal to today plus 7 days.

2. Step 2: Understand the date range

   This means it highlights dates starting from today up to 7 days in the future.

3. Final Answer:

   Dates from today up to 7 days ahead -> Option A

4. Quick Check:

   AND(A1≥TODAY(), A1≤TODAY()+7) = Next 7 days [OK]

Hint: Use AND to set date range between today and 7 days ahead [OK]

Common Mistakes:

• Thinking it highlights past dates
• Ignoring the AND condition
• Confusing TODAY()+7 as 7 days ago

4. You want to highlight dates older than 30 days using conditional formatting. Which formula has an error?

medium

A. =A1<TODAY()-30

B. =A1<=TODAY()-30

C. =A1<TODAY()-30()

D. =A1>TODAY()-30

Solution

1. Step 1: Check each formula's syntax

   Options A, B, and C use correct syntax with TODAY() function and arithmetic.

2. Step 2: Identify the error in =A1<TODAY()-30()

   =A1<TODAY()-30() uses TODAY()-30() which is invalid because 30() is not a function and parentheses after 30 cause error.

3. Final Answer:

   =A1<TODAY()-30() -> Option C

4. Quick Check:

   Numbers like 30 should not have parentheses [OK]

Hint: Numbers don't get parentheses; only functions do [OK]

Common Mistakes:

• Adding parentheses after numbers
• Using greater than instead of less than for older dates
• Omitting parentheses after TODAY

5. You want to highlight all dates in column A that are weekends (Saturday or Sunday) using conditional formatting. Which formula should you use?

hard

A. =WEEKDAY(A1)=6

B. =AND(WEEKDAY(A1)=7, WEEKDAY(A1)=1)

C. =OR(WEEKDAY(A1)=6, WEEKDAY(A1)=5)

D. =OR(WEEKDAY(A1)=7, WEEKDAY(A1)=1)

Solution

1. Step 1: Understand WEEKDAY function

   WEEKDAY returns 1 for Sunday and 7 for Saturday by default.

2. Step 2: Choose correct logical test

   We want to highlight if the date is Saturday OR Sunday, so use OR with conditions WEEKDAY=7 or WEEKDAY=1.

3. Final Answer:

   =OR(WEEKDAY(A1)=7, WEEKDAY(A1)=1) -> Option D

4. Quick Check:

   Use OR for weekend days 1 (Sun) and 7 (Sat) [OK]

Hint: Use OR with WEEKDAY=1 or 7 for weekends [OK]

Common Mistakes:

• Using AND instead of OR
• Wrong weekday numbers for weekend
• Checking only one weekend day