How to Highlight Expiration and Due Dates in Google Sheets

We can use conditional formatting in Google Sheets when we need to highlight expiration and due dates in our spreadsheets.

Conditional formatting allows us to format a cell or a range of cells based on a provided formula. 

Let’s take a look at a quick example where we can implement conditional formatting to highlight overdue dates.

As a project manager, you are responsible for assigning and keeping track of tasks. You created a Google Sheet form where members can add new tasks, the task’s priority, and the due date. 

After a few weeks of using this system, you realize that multiple assigned tasks are overdue. With the dozens of tasks recorded, it was difficult to pinpoint which tasks were overdue or even close to the due date.

With the conditional formatting function, we can easily format our due date column so that overdue tasks can stand out. This is possible because Google Sheets allows us to compare dates with one another as if they were numbers. We can identify how often a task is overdue and format the cell accordingly.

This use case is just one way to use this Google Sheets trick. This may also be useful for keeping track of an inventory, where many items may be monitored for their expiration date.

Now that we know when we can use conditional formatting for overdue dates let’s explore a real-life example of this.

 

 

A Real Example of Highlight Expiration and Due Dates in Google Sheets

This section will show you real examples of conditional formatting being used in a Google Sheets spreadsheet to keep track of dates.

The example below shows a table that keeps track of several tasks. Overdue tasks are in red cells. Tasks due on the current date are colored yellow. If tasks still have upcoming due dates, they are colored green.

example of Highlight Expiration and Due Dates in Google Sheets

 

The conditional formatting gives us a quick view of how severely delayed a project is. A quick glance at the above table shows that almost half of the currently assigned tasks are overdue. 

Our conditional formatting follows custom rules that allow us to compare the cell’s date with the current date. We can get the current date by using the TODAY function.

The example below shows these custom formulas in closer detail. This table shows how close the products in our inventory are to being expired. Red indicates that they are one month away from expiry, orange is two months from expiry, and so on.

use conditional formatting to keep track of food soon to expire

 

You can make a copy of the spreadsheet above using the link attached below. 

If you want to start setting up conditional formatting on your due dates in Google Sheets, let’s proceed to the next section for a step-by-step guide!

 

 

How to Highlight Expiration and Due Dates in Google Sheets

This section will guide you into setting up conditional formatting for a due date column. You’ll learn how to use comparison operators on dates to answer the question: is this task overdue?

Let’s follow these steps to start highlighting due dates in Google Sheets:

  1. Select the cell range that holds our date values. In this example, we’ll select D4:D10.
    Identify the dates to check
  2. While the cell range is still selected, click on the Conditional formatting option under the Format menu.
    add conditional formatting to your sheet
  3. You can find a panel on the right-hand side of your screen. This panel will allow you to change the formatting rules for the selected cell. Let’s add a rule that checks if the date is greater than the current date.

    We can use the formula =D4<TODAY(). Afterward, we can change the background color under the Formatting style section. In this case, we’ll highlight overdue dates with a red background color.
    add formula to Highlight Expiration and Due Dates in Google Sheets
  4. Your table should now look something like the table below.
    overdue dates should now be highlighted red
  5. You can add additional rules to your cell range. In the example below, we added different rules for upcoming tasks and tasks due today.
    Highlight Expiration and Due Dates in Google Sheets

 

Frequently Asked Questions (FAQ)

  1. How do I find out if a date is x amount of days away from the due date?
    Finding this out requires simply adding x days to the current date. We can then compare this new date with the due date. For example, if you want to flag tasks 7 days away from the due date, find out the date seven days from today. We can do this with =TODAY()+7. If the due date is less than or equal to this future date, then our task will be flagged.
  2. What does the EDATE function do?
    If you want to check if a date is one or two months away from its due date, we can use the EDATE function instead. The function accepts a date and increments it by a given number of months. You can learn more about the EDATE function in our detailed guide for the function.

 

This step-by-step guide shows how easy it is to use conditional formatting to highlight overdue dates.

Highlighting expiration dates and due dates is just one example of how you can make your Google Sheets spreadsheets more intuitive and easy-to-read. With so many other Google Sheets functions out there, you can surely find one that suits your needs.

Are you interested in learning more about what Google Sheets can do? Stay notified of new guides like this by subscribing to our newsletter!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

1 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like