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
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,
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
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()
- You may get the result as this,
Incorrect result
- Simply select all the dates and get them to ‘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
Specify the condition for formatting
- All items which are overdue are now highlighted!
Result
