Search form

Use Conditional Formatting to highlight due dates in Excel

Excel's Conditional Formatting feature is incredibly powerful. It allows you to apply formatting to cells or ranges of cells based on criteria that you can choose. You can even use the value of other cells in the worksheet as part of your conditional formatting criteria.

This lesson uses a Project Checklist example to demonstrate how to use Conditional Formatting. The lesson is in two parts.

  • First, it shows you how to apply a format to a cell containing a date where that date is in the past - this allows us to see which of the tasks in our project are overdue.
  • Second, it extends the criteria for the formatting rule so that only those tasks where the due date is in the past AND the task is still open will be highlighted - at a glance, we can tell which tasks are overdue and need attention.

Note that Excel's Conditional Formatting feature includes an option to highlight dates according to a range of pre-configured conditional formatting rules. These are simple and easy to apply but the problem for our scenario is that there isn't a built-in conditional formatting rule that applies to all dates in the past - the best you can do with the built-in rules is to format cells containing dates that are in the last month. So in this lesson, we'll look at how to apply the formatting to cells containing all dates in the past, not just those in the last month.

Applying Conditional Formatting to Dates

Conditional Formatting in Excel allows you to format one or more cells based on the values in those cells. Excel offers a set of standard conditional formatting options. You can also use custom formulas to decide whether to apply a specific formatting rule to a range of cells. Using a formula as part of your conditional formatting rule is incredibly powerful, since you can incorporate cells from anywhere in your spreadsheet as part of the formula in your conditional formatting rule.

Let's start by looking at the spreadsheet that we're going to work with. This is a simple Project Checklist spreadsheet that contains the tasks that need to be completed. Each task has a status and a due date.

Excel conditional formatting lesson, sample project checklist spreadsheet

Format our cells by comparing the Due Date to the current date

In this section, we'll look at how to create a conditional formatting rule that formats the cells in the Due Date column if the task was due on a date that is in the past.

  • First, select the cells you want to format:
    Excel Conditional Formatting example, select cells to apply conditional formatting rule to
  • Next, click the Conditional Formatting button on the toolbar:
    Excel conditional formatting toolbar button
  • Choose the Highlight Cells Rules option, and then select Less Than ....
    Excel Conditional Formatting toolbar button options
    You may also notice another option in the list of Highlight Cells Rules, A Date Occurring. This is sometimes a useful option (e.g. highilight dates in the last 7 days), but it doesn't work in this scenario because it doesn't give us an option to choose all past dates.
  • You'll now see a dialog box like the one below in which Excel has entered a date as the LESS THAN rule (note that Excel has entered a date automatically:
    Excel conditional formatting, less than dialog box
  • At the same time, you may notice the selected cells have been formatted as a preview of the current rule, as shown here:
    Excel conditional formatting, preview of formatted cells based on current rule
  • We're nearly there. As you can see, this conditional formatting rule will format all cells (i.e. all the cells we have selected) and format any cells that match our criteria with red text and light red shading. However, our rule is not quite complete - we haven't finished configuring our criteria. Remember that we want only want this rule to format cells that are in the past, i.e. before today. To do this, we'll replace the date shown with the TODAY() function which, conveniently, returns today's date.
  • Here's how your completed rule should look once you enter the TODAY() function. Note that you must put the = sign at the beginning of the function so that Excel knows you're entering a formula rather than just a text value:
    Excel conditional formatting LESS THAN rule using the TODAY() function
  • Once you've done this, you can click OK and the rule will be saved and applied as shown here:
    Excel conditional formatting example with a past-due date format applied

We've now completed the first part of this lesson - formatting a range of the dates in our spreadsheet so that cells containing Due Dates that are in the past will be formatted in red, as shown above. The use of the TODAY() function means that the spreadsheet will update automatically each day when you open or edit the spreadsheet.

Format our cells based on the Due Date AND the task Status

In this section, we're going to extend our conditional formatting rule so that cells with past dates will only be formatted in red if the related task is still outstanding.

To do this, we need to edit the rule we created in the previous section (rather than creating a new rule). To do this, follow these steps:

  • Select the cells you want to format (i.e. the cells we formatted in the previous section)
  • Click the Conditional Formatting button and choose Manage Rules. The Conditional Formatting Rules Manager will appear:
    Excel Conditional Formatting Rules manager, showing a date rule
  • Select the rule that needs to be edited, then click the Edit Rule ... button. As you'll see from the picture below, this looks like a version of the rule we created earlier, albeit with more information:
    Excel Conditional Formatting, editing a date rule
  • At this point, we need to change our formatting rule to incorporate both of our criteria. Unfortunately, the Less Than rule we chose earlier will not longer work for us since it can't handle multiple criteria. Instead, we'll change the Rule Type to Use a formula to determine which cells to format. When you do this, the rule description will change, as shown here:
    Excel Conditional Formatting, example showing a rule based on a formula
  • You may also notice that I've already entered the formula we need:
    • =AND(D5<>"Done",C5<TODAY()
  • Here's how this formula works:
    • The AND function looks at the two arguments inside the brackets and checks that both of them are true. If they are, then the AND function returns TRUE, the criteria for the rule are met and the formatting will be applied. If one or both of them are not true, then AND returns FALSE instead and the formatting is not applied. You can learn more about the AND function and its companion, the OR function, in our lesson Using logical operators and functions in Excel.
    • The first argument in the AND function is D5<>"Done". This checks the Status value in column D to see if it is set to Done. If it isn't, this argument is TRUE. Note that this part of the formula uses relative references. When evaluating the status in row 5 (the first row in our table), Excel will look in D5. When evaluating row 6, Excel will look in D6, and so on. It's worth noting that I could have written the formula as D5="Open", to check if the status was set to Open. However, my projects usually have a number of status values that can be applied while a task is being worked on (Open, Pending, Waiting, Deferred) but only one status when it is done.
    • The second argument in the AND function is C5<TODAY(). This is an alternative version of the rule we created in the first section - it simply looks at the value in the Due Date column to see if it is less than today's date. If it is, this argument returns TRUE.
  • Once you have completed writing your formula, you can click OK to return to the Rules Manager, which now looks like this:
    Excel Conditional Formatting, Rule Manager showing a Formula rule
  • Finally, you can click OK to see the results of this formula on the cells we selected. As you can see, the only row where our red formatting has been applied is row 7, where the Due Date is in the past, and the Status is Open rather than Done:
    Excel Conditional Formatting, date and status rule applied

Conditional Formatting Lesson Summary

​In this lesson we looked at how to create a conditional formatting rule that formats a cell containing a date if that date was prior today. We also looked at how to extend our conditional formatting rule to include criteria based on cells other than the cell that is to be formatted. In this example we only wanted to apply the formatting if the Status column was not set to Done, indicating that the task was overdue.

Conditional Formatting is a very powerful feature of Excel which can be used in a number of different ways to format cells and data automatically based on both simple and complex criteria. For another example of conditional formatting in action, check out our lesson Shade alternate rows in an Excel spreadsheet on a Mac. Even if you don't use Excel on a Mac, you'll recognise the same elements of Conditional Formatting rules, and will be able to apply the same concepts in Excel for Windows. It's also worth noting that Google Spreadsheets also support a Conditional Formatting feature; the formula I used in this example can be copied and pasted into Google's Conditional Formatting rules to achieve the same result.

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.

Add a comment to this lesson

Comments on this lesson

Conditional Highlighting

Hi,

I am looking at a spreadsheet showing all emloyees' vacations in a tracker.
I would like a red highlight to appear (perhaps in the totals column at the far right of the sheet, or on the team itself in the team column), when there is more than 40% of a team away at the SAME time.

Currently the letter "V" indicates vacation, there are rows for each employee and columns showing the dates in chronological order.

Thank you in advance to anyone who can shed some light on this!

Upa

Highlight a Cell depending of the value of another cell

Hi,

I have created a reservation list for the max. capacity of tables in a restaurant.

In the one column will be entered the number of guests, the next column calculates the amount of tables needed.

In one cell will appear the number of tables used in total with the note if tables will still be available or if the restaurant is full.

I am looking now for a formular in the conditional formating that highlights automatically the cells in the column for the needed tables if there is going to be an overbooking with the next reservation made.

To make it more understandable:

The formular of the cells for the tables needed is

=IF(D6=0,"",IF(D6<5,1,IF(D6<7,2,IF(D6<9,3,IF(D6<11,4,IF(D6<13,5,IF(D6<15,6,IF(D6<17,7))))))))
and
=IF(K6=0,"",IF(K6<5,1,IF(K6<7,2,IF(K6<9,3,IF(K6<11,4,IF(K6<13,5,IF(K6<15,6,IF(K6<17,7))))))))

For both columns the cell counting the total of tables needed
=IF(K31>24,"FULL","AVAILABLE")

Based on the total of tables needed I want the cells in the columns for the reservation of the tables to change the color as soon as the K31 shows FULL.

Thanks for your help

Formatting cells with dates

I'm attempting to write a format for our spreadsheet that tracks post-mammogram actions. I need to format the "date of exam" field to remain green for seven days and then turn red until a date is entered into the "date results sent" cell. Thanks in advance!!

Highlighting overdue dates

I am creating a spreadsheet with all staff training. Some of the Training is Mandatory and also needs to be renewed every ..12 months for example. Is there a formula that I can input that will change the date to amber if the training is coming up for renewal (e.g 4 weeks before due, and will also incorporate an additional formula that will change the date to red if overdue.

Two conditional formatting rules required

Hi Michelle

You'll need two conditional formatting rules for this, each of which applies its formatting based on a formula.

If your Due Date is in cell D6, then the formulae you need will look something like this:

  1. =(TODAY()-A6)<=28 (use this for the due in less than 28 days rule - it checks if the due date is less than 28 days away)
  2. =TODAY()>A6 (use this for the overdue rule - it checks if the due date is before today).

It doesn't matter which order these rules appear in when looking at the list of conditional formatting rules.

I hope that helps. Feel free to reply with any further questions.

Regards

David

Conditional Formatting for Expiry dates

HI there Dave

Please can you help me with a formula for columns in which I need to apply the following:

date due to expire in 60 days, then another colour if it is due to expire in 30 days, and a thrid colour if it has expired.

I have tried various ways of formatting but I cannot get 3 sets of conditions/rules to work together to change colour as appropriate.

Many thanks

Sheila

Conditional Formatting

I'm having a hard time utilizing the conditional formatting function. I am managing several pilots and I have a spreadsheet that shows when they are due certain things based upon their birth month. For example, a pilot has to take an annual exam based upon his birth month.
If he is within 30 days of his birth month or over 365 days, the cell will turn red.
If he is within 60 days but more than 30, it will turn yellow.
If he is within 90 days but less more than 60, it will be green.
All cells will have no color if he has a current date (within 90 days).

Multiple conditional rules, each with a separate formula

Hi Chuck

The key here will be to use formulas that calculate to TRUE or FALSE.

The challenge will be that you want to do things based on a comparison of the pilot's birthday and the current date.

That could be simple - your formula could be something like the following (assume the birthday is entered in C6):

=(C6-TODAY())<=30.

This will be true if the future birthday is 30 days or less from today.

Your challenge, as you've described it, is to calculate the difference between today and the first day of the month in which the pilot's birthday occurs. That's a bit harder to achieve:

First, here's a formula to return the first day of the birthday month:

=DATE(YEAR(C6),MONTH(C6),1)

This formula returns the first day of the month for the date shown in C6. This is a date value, so we can use it in a revised version of our conditional formula above:

=(DATE(YEAR(C6),MONTH(C6),1)-TODAY())<=30.

This new formula compares the first day of the month in which the pilot's birthday occurs with today. If today is within 30 days of the first day of the birthday month, this formula will return TRUE, which would trigger the conditional rule this formula is attached to.

It's slightly flawed, though, since it assumes the pilot's next birthday is happening this year, not next (e.g. what if it's December, and the birthday is in January). This formula partially addresses this scenario:

=(DATE(YEAR(C6)+(MONTH(TODAY())>=MONTH(C6),MONTH(C6),1)-TODAY())<=30.

I suspect even this formula still needs some work, e.g. what happens if TODAY() and the birthday are both in the same month, but the birthday has already happened. Should it go red? It will do with this formula.

I realize that I haven't given you a complete solution here, but hopefully it will set you on the right path. Feel free to ask more specific questions (with formula examples) here if you are still stuck.

Regards

David

Preventative Maintenance Tracker

Hi

I'm working on a spreadsheet to track all our downhole tools for their maintenance due dates.
I have a column where the current maintenance was done and another column for the due date which is 12months/365days from when maintenance was last done.
I'd like to apply a condition in which the dates under the due date column can turn Green if its within 12months and if its past 12months I'd like these cells to turn red.

Cheers
Andrew

Help

Hi,

If we would like to make a Condition Formatting formula for the cell to turn Yellow after any of the dates passed 1 week of the Reference Date and to turn Red after the date passed 2nd week (at this point, Yellow should no longer showing. Only Red).

Ex: 12th = Ref, 19th-26th = Yellow and 27th onwards = Red

Please help to advise. Thanks.

12th = Ref, 19th-26th = Yellow and 27th onwards = Red

I have a cell with a date inside of and a formula of =DATE(YEAR(H7) + 2, MONTH(H7), DAY(H7)) the question is how can i make this cell yellow at 120 days before due date and red 60 days before due date, PLEASE HELP THANK YOU

conditional Formatting help

hello. I am trying to update a number 2.5 every 30 days starting on the 1st of the month. I would also like that number to change colors from green 0-44 to yellow at 45-59 and red at 60+

so if my number starts out at 10 in 30 days starting on the 1st the number updates to 12.5 and then in 30 more days 15 etc. Any help you can provide me would be appreciated. Thank you

Please help!

I'm trying to create a heat map of tasks to see what's overdue. The logic is, is the deeadline in the past? If so is the task complete? If falseenter incomplete, if true, enter complete. The issue is I'm really struggling with future deadline dates. I've tried this formula =IF(AND(Gant!G13<>"100%",Gant!E13<TODAY()),"Overdue","Complete") which works great except fails completely with future dates as I'm asking for dates less than today.

Ideally, for future tasks was in the future it would enter the future date, if already complete it would say complete.

Many thanks for your help :)

Conditional Formatting

I am trying to format cells red, yellow, or green based on two conditions (1) past their due date and (2) percent complete. If it's past a due date and not complete it's red. If a task is due in two weeks and less than 25% it's red; due in two weeks and between 25 - 75% it's yellow; due in two weeks and >75% green. How do i do this?

Application of 2 argument formula to the rest of the excel

Hi,

So i have used the "AND" formula to apply to the single cell where it checks if the corresponding cell is either "Done" or not and then changes color.

How can i extend the application of the conditional format to do this for all the date cells to check its corresponding status?

eg. =AND($H$3<>"g",$D$3<TODAY())
Where Column D = due date
Column H = status

Do i have to create a conditional formula for every single cell separately?

Application of 2 argument formula to the rest of the excel

Hi,

So i have used the "AND" formula to apply to the single cell where it checks if the corresponding cell is either "Done" or not and then changes color.

How can i extend the application of the conditional format to do this for all the date cells to check its corresponding status?

eg. =AND($H$3<>"g",$D$3<TODAY())
Where Column D = due date
Column H = status

Do i have to create a conditional formula for every single cell separately?

Conditional Formatting - date

Thank you so much. this was quick and awesome to access and get to the answer without struggling to understand where to go. I love the visuals and explanations. Huge thanks

Add comment