Whether it’s delivery date, payment date or a followup date, you want Excel to tell you when it’s overdue.

Use this simple method to highlight (color) all cells that are overdue. The best part is, it is dynamic in nature, meaning it will check automatically on a daily basis whether a particular item is overdue ‘today’ or not.

What we need to do

How your data might be a List of customers with their Order Dates & Payment Credit Period (in no. of days).

Data structure

Data structure

You want to calculate this

  1. Payment Date
  2. No. of days remaining

Then, you want to highlight the records which are overdue. Result will look like,

Result

Result

Steps to create this

  • For the payment date, simply put a formula =B2+C2.
    This will add the Order date with Credit no. of days and give you the resultant Payment Date. Copy the formula down throughout.

    =B2+C2

    =B2+C2

    Copy the formula down throughout

    Copy the formula down throughout

  • For the No. of days remaining, =D2-TODAY()
    =TODAY() will internally generate Today’s date (dynamic, will change automatically everyday). D2-Today() will subtract the 2 dates and give you the difference no. of days.

    =D2-TODAY()

    =D2-TODAY()

  • You may get the result as this,

    Incorrect result

    Incorrect result

  • Simply select all the dates and get them to ‘General’ format.

    General format

    General format

  • Now select the ‘No. of days remaining’ column (without the header) and follow the below steps..
  • Home tab -> Conditional formatting -> Highlight cell rules -> Less than -> 0 (zero) -> Red color (or any other)
    Select column without header

    Select column without header

    Specify the condition for formatting

    Specify the condition for formatting

  • All items which are overdue are now highlighted!
Result

Result