Highlight due dates in Excel – Show items due, overdue and completed in different colors

Posted on May 22nd, 2012 in Excel Howtos , Learn Excel - 26 comments

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything.

Highlight due dates in Excel - Show items due, overdue and completed in different colors

The problem – Highlight due dates in Excel

Lets say you work at Awesome inc. and you have list of to-do items as shown below.

And your problem is,

  • Highlight items & due dates, subject to these conditions
    Criteria for highlighting - highlight if due
  • And of course start working on the items that are due

The Solution – Conditional Formatting

As you can guess, highlighting the due items is easier than actually doing them. First lets look at the solution and then learn why it works.

Lets assume that,

  • The data is in the range – B6:D15, with Items (column B), Due date (C) and Completed?(D)

Highlight if due - data

Solution

  1. Select the entire range (B6:D15) and from home ribbon select conditional formatting
  2. Select New rule
  3. Select the rule type as “use a formula…”
  4. To highlight completed activities
  5. Now write =$D6=”Yes”
  6. And set font color to dull gray from formatting button.
    Highlighting completed items in a different color - highlight if due
  7. To highlight items due in next 7 days
  8. Add one more “use a formula…” rule
  9. Write =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”)
  10. And set fill color to orange.
  11. To highlight items already due
  12. Add one more rule
  13. Write =AND($C6<=TODAY(),$D6<>”Yes”)
  14. And set fill color to red & font color to white.
  15. Done!

Now, the items will be highlighted based on the current date (TODAY)  and change colors as you make progress.

Why does it work? – Explanation

At this point you may have 2 burning questions.

  1. Why does this work?
  2. How the heck am I supposed to ship 100 units of smile.

Lets talk about the solution & understand why it works.

Understanding the highlighting conditions

We have 3 conditions in our highlight table (shown above).

  • If done show in dull gray color
  • If not done & due in next 7 days show in orange color fill.
  • If not done & already due show in red fill, white color

If done:

The first condition is easy to check. We just see if a todo item is completed and then highlight the whole row dull gray color. So we write =$D6=”Yes” as the condition. We use $D6 (not D6) because we want Excel to look at column D (completed?) even when we are highlighting other columns (B – Item, C – Due date).

If not done & due in next week:

This is tricky. We need to check,

If completed is not yes

AND

If due date is with in next week

So we start with an AND formula. We write =AND($D6<>”Yes”

Then to check if due date is in next week, we use MEDIAN formula, like this MEDIAN(TODAY()+1,$C6,TODAY()+7)

So the condition becomes =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”)

conditional formatting rule for highlighting items due in next week

If already due:

This is another simple AND formula =AND($C6<=TODAY(),$D6<>”Yes”)

Rule for overdue items - highlight if items are over due - conditional formatting

Remember:

We need to use $D6 & $C6 (instead of D6, C6) because we want Excel to check Completed & Due date columns. By removing the $ Excel will check relative columns and the conditions would not work!

More: Using relative vs. absolute references in Excel formulas

Now that we understand how this works, give me a big smile. And repeat that 99 more times & you know how to ship 100 smiles :)

Download Example File

Click here to download example file. Break it apart, play with it to understand the whole highlight if due thing.

Note: I use random formulas to generate due dates & completed values. Press F9 to get fresh set of dates. Start typing your own values to remove formulas.

How do you handle dead-lines?

Do you use conditional formatting to see which items are due? I use conditional formatting for this all the time. What techniques you use? Is your dead-line criteria very different than shown above? Please share your tips & ideas with us using comments. I would love to learn from you.

Using Conditional Formatting to deal with Due dates

Here are a few useful articles if you use Excel to track to do items & reminders.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 Responses to “Highlight due dates in Excel – Show items due, overdue and completed in different colors”

  1. Peter says:

    Nice!
    Been using something like this for quite a while. Most companies use deadlines so this would be usefulle for everyone.

    • Gordon Bennett says:

      This is exactly what I need! However, I simply cannot get it to work! I’m using Excel 2010 and am completely replicating what you are doing, but it simply will not do it. Any advice from anyone would be greatly appreciated. PS EnableFormatConditions is set to True

      • S says:

        I had the same issue, but I think it was a formatting issue.

        Copy the formula from this website but delete the “” quotation marks. Type them back in from your own keyboard within the Conditional Formatting dialog boxes.

        Hope this helps.

  2. Love it – although I often turn my books late anyway :(

  3. Excel Challenged says:

    I am still not sure how the MEDIAN function works. When I print the value, it prints a random 5 digit number, But, when used in the formula, it does equate to the date which is weird. Why does it behave such ?
    =MEDIAN(TODAY()+1, $C6,TODAY()+7) in a cell returns a 5 digit # while
    =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6<>”Yes”) this seems to work just fine in Conditional formatting.

    • Shalabh Jain says:

      The 5 digit number that you see is the number of days elapsed since 1st Jan 1900 to the date in question. Excel uses this as a basis for all numeric operations on dates.

    • Hui... says:

      Following what Excel Challenged mentioned above,

      Change the Number format of the cell to DD MMM YY or whatever format you want.

  4. Robert Clark says:

    Very clever use of Median – wouldn’t have thought of using it!

    @Excel Challenged:
    “The MEDIAN function, one of Excel’s statistical functions, shows you the middle number in a data list.
    Middle, in this case, refers to arithmetic value rather than the location of the numbers in a list.
    If there is an even set of numbers, the median is the average of the middle two values. ”

    So, if we give three dates (start date, target date, end date), median will return the middle date of the three.  If target date is between the start and end dates, then the median function will return this.  If the target date is before the start date, then the median function will return the start date. If the target date is after the end date, then the median function will return the end date.

  5. Jocelyn says:

    Can you change the dates to only fall on week days?  How?

    • Hui... says:

      @Jocelyn

      Can you please explain more about what you want to do?

       

      • Jocelyn says:

        We have an accounts payable spread sheet that has due dates of bills on it.  I would like the dates to only fall on weekdays since we do not work weekends.  Thank you for your help.

  6. Sameer says:

    Ok, lets say if due date is 30 days plus date of invoice; then;

    =IF(AND(WEEKDAY(C13+30,2)>=1,WEEKDAY(C13+30,2)<=5),C13+30,IF(WEEKDAY(C13+30,2)=6,C13+32,C13+31))

    In the above; IF condition will check if weekday of due date is between 1 and 5 ie. 1 being monday and 5 being Friday.
    Then if this is true then it adds 30 days to the date of invoice, else it will add 31 days if due date is falling on sunday or add 32 days from date of invoice if its Saturday…

    Hope i got it right… Chandoo what do u think?

  7. David says:

    How do I have the cell highlight just every 14 days after the date I manually enter in?

    I contact someone on the 1st and I want the cell to highlight on the 14th and that’s it. 

  8. Jennie says:

    brilliant – this is just what I need,
    I wil be adding tasks to the spreadsheet so how can I copy the conditional formatting to other cells in the relevant columns please?
     
    Thank you

  9. djograd says:

    Hi Chandoo! Very nice! Can this be done also in Google Spreadsheet? If not can you please show us a workaround or similar. Thank you. 

  10. LARRY says:

    i have a formula i need to work out. im more of a novice and enjoy working with excel, however some of the more intense formulas escape me. i have followed this thread quite a bit and believe that i can use this to solve my problem, however i cant seem to grasp the specific verbage of the formula i need. i have 2 colums, one for PT test complet, one for PT test due, i have used a simple format of (=column1+180) to get my due date, what i need is to get the due date to highlight color 1-150 days after green, 151to 180 yellow and 181 days after red. help???

    • Chandoo says:

      Hi Larry… Thanks for your comments.

      The simplest way to do this is, add an extra column (lets call it completed).

      Write the formula =today()-column1

      Now, use this column value to color the cells accordingly.

  11. LARRY says:

    im not quite grasping this, can you give me an example?

  12. LARRY says:

    when i do this, it returns the date as 1900

  13. Steve says:

    Good stuff! It worked well for me, however, I now wish to apply the same CF to 2 columns, instead of a cell selction. When I do this, all the blank cells in the columns turn red. Is there a formula for “if blank do not format” or similar?

    • Steve says:

      No problem, I’ve worked it out…modified the last formula to read….
      =AND($D1>0,$D1<=TODAY()+28,$E1<>”YES”)
      The $D1>0 statement means that any blank cells are not formatted.

  14. Patricia says:

    I’m wanting to figure out how i would do this for my job…i need to have orders signed by doctors and i’m wanting it to let me know when two weeks has passed and the order has not been received.

  15. Yris says:

    Hello,

    I need help…..

    I am trying to make an employee spread sheet that will tell me when training is expired or about to expired or completed. I have an idea but I am still having trouble with the formula, I can do it in one cell but not for an entire column. It is not that big, one column for the employee names, next columns for the dates of the trainings. if training are completed I want them to turn green, if training are 30 days before expiration i want them to turn yellow and if they are expire i want them to turn red. any help please…
    thank you

  16. Andries says:

    Please help if you can!
    I need to make a daily list of jobs with their due dates and then have three different things happening.
    1. When a job is completed/ticked off, it must removed from the list automaticaly
    2. From one day, before the due date, to 12:00am on the due date the cell colour of the whole row must be orange.
    3. After 12:00am on the due date the cell colour of the whole row must be red and stay red untill its ticked off as completed.

    I’ll really appreciate help in this, I’ve tried several formulas but I just don’t get it quite right.

    Regards

  17. Mehaboob says:

    Hi sir
    it is very use full ,I have a doubt after making the due date ex:- in one sheet i have many customer each one have diffrent due due date and I make 12 sheet with month name is it possible to send the due date next sheet by month name , ex ; in JANUARY D1 due date is 12/9/13 I want send this to SEPTEMBER D1,and JAN D2 14/3/13 it need to go to march like this for every month

    I hope you can help me in matter I am really sorry my English is not good
    thanks Mehaboob

  18. Bryson says:

    I have input all the formulas as the tutorial says but if there is no date put into the field it turns everything red. In other words I am trying to make a template at work with due dates and the orange part works great but everything is red unless I put a date into the cell. Any help would be awesome.

Leave a Reply