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).
You want to calculate this
- Payment Date
- No. of days remaining
Then, you want to highlight the records which are overdue. Result will look like,
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. - 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. - You may get the result as this,
- Simply select all the dates and get them to ‘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)
- All items which are overdue are now highlighted!