Conditional formatting with dates in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is conditional formatting in Excel?

Conditional formatting lets you change how cells look based on their values or other rules. For example, you can color dates that are past today in red.

beginner

How do you highlight dates that are before today using conditional formatting?

Use a formula rule with =A1<TODAY() where A1 is the first cell in your range. This colors all dates before today.

intermediate

What formula highlights dates within the next 7 days?

Use =AND(A1>=TODAY(), A1<=TODAY()+7). This checks if the date is today or up to 7 days ahead.

beginner

Why use TODAY() in date conditional formatting?

TODAY() gives the current date. It updates automatically every day, so your formatting stays correct without changes.

intermediate

How to apply conditional formatting to a whole column of dates?

Select the whole column, create a new rule with a formula like =A1<TODAY(), and make sure the formula uses the first cell of the column. Excel applies it to each cell correctly.

Which formula highlights dates that are exactly today?

A =A1<=TODAY()

B =A1<TODAY()

C =A1>TODAY()

D =A1=TODAY()

The formula =A1=TODAY() checks if the date in A1 is exactly today.

What does the formula =A1>TODAY()+7 highlight?

A Dates before today

B Dates within the next 7 days

C Dates more than 7 days in the future

D Dates exactly 7 days from today

It highlights dates that are more than 7 days after today.

How do you start creating a conditional formatting rule for dates?

A Type a formula directly in the cell

B Select cells, then choose Conditional Formatting > New Rule > Use a formula

C Use the Sort feature

D Change the cell format to Date

You create conditional formatting rules by selecting cells and choosing Conditional Formatting > New Rule > Use a formula.

Which function gives the current date in Excel?

A TODAY()

B DATE()

C NOW()

D CURRENTDATE()

TODAY() returns the current date without time.

What happens if you use relative cell references incorrectly in conditional formatting formulas?

A Formatting may apply to wrong cells

B Excel shows an error message

C Formula automatically fixes itself

D Nothing happens

Incorrect relative references can cause formatting to apply incorrectly across the range.

Explain how to use conditional formatting to highlight dates that are overdue (before today).

Think about how to compare each date to today's date.

Describe the role of the TODAY() function in date-based conditional formatting.

TODAY() keeps your formatting dynamic.