Video conditionally format dates

OverviewTranscript



Say you want to see, at a glance, what tasks in a list are late. In other words, those tasks with due date dates before today. Or which tasks aren't due for a week or more. Watch this video to learn how.

Want more?

Apply conditional formatting to quickly analyze data

Add, change, find, or clear conditional formats

Take conditional formatting to the next level

I want to see at a glance what tasks in the list are late. In other words, those tasks with the due date before today.

I select the cells in the Due date column, click the Quick Analysis button, click Less Than.

I could type today's date, but I'd have to update the conditional formatting every day.

That would get tedious really fast. Instead, I type the TODAY function, which returns today's date.

Equals sign, TODAY, opening parenthesis, and a closing parenthesis.

I am going to leave the formatting set to the default red option, and click OK.

Tasks that are overdue show in red and are easy to pick out at a glance.

I also want to see tasks that aren't due for a week or more. These tasks are in good shape.

Configuring conditional formatting for this is similar to what we did for overdue tasks, except I click Greater Than, I type the TODAY function, a plus sign, and 7.

In other words, a week from today.

Notice that there are many formatting options, including Custom Format.

I'll select the green option, and click OK.

Tasks that aren't due for a week or more show in green.

I'd like to see at a glance what tasks are coming due over the next week, so I can keep a close eye on them.

Quick Analysis doesn't have this option, so I click the Conditional Formatting button on the HOME tab where there is this option.

Point to Highlight Cell Rules, and click Between.

In the left date field, I type the TODAY function.

In the right date field, I type the TODAY function, a plus sign, and 7.

I select the yellow formatting option, and click OK.

Tasks that are due within seven days are formatted in yellow.

Now I have a list of tasks that, at a glance, I can see how they are doing against the schedule.

Up next, Conditionally formatting text.