Excel - Conditionally format dates Tutorial

In this video, you will learn how to conditionally format dates in Microsoft 365. The video covers the process of highlighting overdue tasks in red, tasks due in a week or more in green, and tasks due within seven days in yellow.
This will help you easily identify and track the status of tasks based on their due dates.

2:38
4093 views

Available in Microsoft365

Objectifs :

This video aims to teach viewers how to use conditional formatting in Excel to visually manage tasks based on their due dates. By the end of the video, viewers will be able to identify overdue tasks, tasks due in a week or more, and tasks due within the next seven days using color-coded formatting.

Chapitres :

Identifying Overdue Tasks
To quickly identify overdue tasks, start by selecting the cells in the Due Date column. Click on the Quick Analysis button, then choose the 'Less than' option. Instead of manually typing today's date, use the TODAY function, which automatically returns the current date. Leave the formatting set to the default red option and click OK. This will highlight all overdue tasks in red, making them easy to spot at a glance.
Highlighting Tasks Due in a Week or More
Next, to highlight tasks that are due in a week or more, the process is similar to identifying overdue tasks. Click on the 'Greater than' option and again use the TODAY function to set the criteria for a week from today. There are various formatting options available; select the green formatting option and click OK. Tasks that are due in a week or more will now be highlighted in green, indicating they are in good shape.
Monitoring Upcoming Tasks
To keep track of tasks that are coming due within the next week, click on the Conditional Formatting button located on the Home tab. From there, point to 'Highlight Cell Rules' and select 'Between'. In the left date field, input the TODAY function, and in the right date field, type TODAY + 7. Choose the yellow formatting option and click OK. This will format tasks due within the next seven days in yellow, allowing for easy monitoring.
Summary of Task Management
By applying these conditional formatting techniques, you can effectively manage your tasks in Excel. Overdue tasks will be marked in red, tasks due in a week or more will be green, and tasks due within the next seven days will be highlighted in yellow. This color-coded system provides a clear visual representation of your task schedule, enabling you to stay organized and on track.

FAQ :

How can I identify overdue tasks in my spreadsheet?

You can identify overdue tasks by selecting the cells in the Due date column, using the Quick analysis button, and applying conditional formatting to highlight tasks with a due date before today in red.

What is the TODAY function and how do I use it?

The TODAY function returns the current date. You can use it in your spreadsheet by typing 'TODAY()' in a cell to automatically get today's date.

How do I format tasks that are due in a week or more?

To format tasks due in a week or more, use conditional formatting by selecting 'Greater than' and entering the TODAY function plus 7 days, then choose a formatting option like green.

Can I see tasks that are due within the next week?

Yes, you can see tasks due within the next week by using conditional formatting. Select 'Between' and enter the TODAY function and TODAY function + 7 days, then choose a formatting option like yellow.

What are the benefits of using conditional formatting?

Conditional formatting helps you quickly visualize the status of tasks, making it easier to manage deadlines and prioritize work.

Quelques cas d'usages :

Project Management

In project management, you can use conditional formatting to track task deadlines. By highlighting overdue tasks in red, you can quickly identify which tasks need immediate attention, improving project oversight.

Personal Task Management

For personal task management, applying conditional formatting allows you to visually organize your to-do list. You can easily see which tasks are overdue, upcoming, or due in a week, helping you prioritize your daily activities.

Team Collaboration

In a team setting, using conditional formatting in shared spreadsheets can enhance collaboration. Team members can quickly identify their responsibilities and deadlines, ensuring everyone is aligned and aware of upcoming tasks.

Sales Tracking

Sales teams can utilize conditional formatting to monitor follow-up tasks with clients. By marking overdue follow-ups in red, the team can ensure timely communication and improve customer relationships.

Event Planning

Event planners can apply conditional formatting to track tasks related to event preparation. By highlighting tasks due soon, planners can ensure all aspects of the event are completed on time, leading to successful execution.

Glossaire :

Due date

The date by which a task must be completed.

Conditional formatting

A feature in spreadsheet applications that allows users to apply specific formatting to cells that meet certain criteria.

TODAY function

A function in spreadsheet software that returns the current date.

Quick analysis

A tool in spreadsheet applications that provides quick insights and options for data analysis.

Formatting options

Various styles and appearances that can be applied to cells in a spreadsheet, such as colors and fonts.

Highlight Cell Rules

A set of rules in conditional formatting that allows users to highlight cells based on specific criteria.

00:00:08

In other words, those tasks with a due date before today.

00:00:13

I select the cells in the Due date column;

00:00:16

click the Quick analysis button; click Less than.

00:00:22

I could type today's date, but I'd have to update

00:00:25

the conditional formatting every day.

00:00:28

That would get tedious really fast.

00:00:31

Instead, I type the Today function which returns today's date

00:00:36

=TODAY().

00:00:45

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

00:00:51

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

00:00:58

I also want to see tasks that aren't due for a week or more.

00:01:02

These tasks are in good shape.

00:01:05

Configuring conditional formatting for this is similar to

00:01:09

what we did for overdue tasks, except I click Greater than.

00:01:14

I tape the TODAY function,

00:01:19

+ 7.

00:01:23

In other words, a week from today.

00:01:26

Notice that there are many formatting options, including Custom format;

00:01:31

I'll select the green option and click OK.

00:01:36

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

00:01:42

I'd like to see at a glance what tasks are coming due

00:01:45

over the next week, so I can keep a close eye on them.

00:01:49

Quick analysis doesn't have this option,

00:01:53

so I click the Conditional formatting button on the Home tab,

00:01:57

where there is this option.

00:02:00

Point to highlight Cell rules, and click Between.

00:02:04

In the left date field I type the TODAY function;

00:02:09

in the right date field, I type the TODAY function + 7.

00:02:19

I select the yellow formatting option, and click OK.

00:02:25

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

00:02:31

Now we have a list of tasks that, at a glance, I can see

00:02:34

how they're doing against the schedule.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2495 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES