Excel Formula to Show Invoice Status and Days Until or Past Due
Question details
The user needs a formula that displays an invoice's due status and the number of days until or past its due date.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An invoice worksheet contains due dates that need to be compared with a reference date.
- Observed behavior
- The desired result is a blank for a missing due date, an overdue count for past dates, Due Today for the current date, or a count of days remaining for future dates.
Identify the due date cell and the cell containing the comparison date. The example uses J5 for the due date and B3 for the comparison date; both should contain valid dates without time values.
Use an IF Formula to Display Invoice Due Status
Use this formula to show the appropriate status for past, current, future, or missing due dates.
In cell B3, enter =TODAY() to compare invoices with the current date, or enter another valid date.
Enter the invoice due date in cell J5, or leave J5 empty if no due date is available.
Select an empty result cell, such as K5, and enter: =IF(J5="","",IF(J5-$B$3<0,"Overdue by: "&($B$3-J5)&" day(s)",IF(J5-$B$3=0,"Due Today","Invoice is due in: "&(J5-$B$3)&" day(s)")))
Drag the fill handle from K5 down the status column. The due date reference changes by row, while $B$3 remains fixed.

Track Invoice Due Dates in WPS Spreadsheets
Use the same IF formula in WPS Spreadsheets to show whether an invoice is overdue, due today, or due in a number of days.
- 1. Open the invoice worksheet: Open WPS Spreadsheets and select File > Open to choose your invoice workbook.
- 2. Set the comparison date: Enter =TODAY() in B3, or enter the date you want to use for comparison.
- 3. Enter the invoice formula: With the due date in J5, select K5 and enter: =IF(J5="","",IF(J5-$B$3<0,"Overdue by: "&($B$3-J5)&" day(s)",IF(J5-$B$3=0,"Due Today","Invoice is due in: "&(J5-$B$3)&" day(s)")))
- 4. Fill the status column: Drag the fill handle from K5 down to apply the formula to the remaining invoice rows.

Frequently Asked Questions
How can I make the status update each day?
Enter =TODAY() in B3. The formula compares each due date with that cell, which updates when the workbook recalculates.
Why is the status blank when there is no due date?
The first IF condition checks whether J5 is empty and returns an empty string if it is.
Can I use a comparison date other than today?
Yes. Enter the date you want to compare against in B3 instead of =TODAY().
Why does the formula use $B$3 instead of B3?
The dollar signs keep the comparison date fixed in B3 when you copy the formula down to other invoice rows.
Why does my formula show an error or an unexpected day count?
Check that J5 and B3 contain valid date values rather than text, and remove any time values if you want whole-day comparisons.




