Conditionally format dates

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.