Conditional formatting with dates in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to highlight cells with dates greater than today.

Excel

=A1 [1] TODAY()

Drag options to blanks, or click blank then click option'

A<

B>

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' causes the wrong dates to be highlighted.

Using '=' only highlights dates exactly equal to today.

The formula checks if the date in cell A1 is after today.

2fill in blank

medium

Complete the formula to highlight dates within the last 7 days.

Excel

=AND(A1 >= TODAY() [1] 7, A1 <= TODAY())

Drag options to blanks, or click blank then click option'

A*

B+

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' adds days, which checks future dates instead of past.

Using '*' or '/' causes errors in date calculation.

The formula checks if the date in A1 is between today and 7 days ago.

3fill in blank

hard

Fix the error in the formula to highlight dates older than 30 days.

Excel

=A1 [1] TODAY() - 30

Drag options to blanks, or click blank then click option'

A<

B<=

C>=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' highlights dates newer than 30 days ago.

Using '>=' or '<=' includes the boundary date incorrectly.

To highlight dates older than 30 days, check if A1 is less than (before) today minus 30 days.

4fill in blank

hard

Fill both blanks to highlight dates that are weekends.

Excel

=OR(WEEKDAY(A1) = [1], WEEKDAY(A1) = [2])

Drag options to blanks, or click blank then click option'

A1

B7

C6

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using '6' instead of '7' highlights Fridays instead of Saturdays.

Using only one day misses half the weekend.

In Excel, WEEKDAY returns 1 for Sunday and 7 for Saturday by default.

5fill in blank

hard

Fill all three blanks to highlight dates in the current month.

Excel

=AND(MONTH(A1) = [1], YEAR(A1) = [2], A1 <= [3])

Drag options to blanks, or click blank then click option'

AMONTH(TODAY())

BYEAR(TODAY())

CTODAY()

DDAY(TODAY())

Attempts:

3 left

💡 Hint

Common Mistakes

Using DAY(TODAY()) instead of MONTH(TODAY()) or YEAR(TODAY()).

Not checking the year causes wrong matches across years.

This formula checks if the date in A1 is in the same month and year as today and is not a future date.