Function Problems

Excel Formula to Show Invoice Status and Days Until or Past Due

Olivia MillerOlivia Miller Nov 18, 2026 868 views

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.

Show Invoice Status and Days Until or Past Due in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set the comparison date

In cell B3, enter =TODAY() to compare invoices with the current date, or enter another valid date.

2
Enter the due date

Enter the invoice due date in cell J5, or leave J5 empty if no due date is available.

3
Enter the status formula

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)")))

4
Apply it to other invoices

Drag the fill handle from K5 down the status column. The due date reference changes by row, while $B$3 remains fixed.

Use an IF Formula to Display Invoice Due Status
Check date values: The calculation counts whole days when J5 and B3 contain dates without time values. Dates stored as text or dates with times may produce errors or unexpected results.
Invoice tracking in WPS Spreadsheets

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. 1. Open the invoice worksheet: Open WPS Spreadsheets and select File > Open to choose your invoice workbook.
  2. 2. Set the comparison date: Enter =TODAY() in B3, or enter the date you want to use for comparison.
  3. 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. 4. Fill the status column: Drag the fill handle from K5 down to apply the formula to the remaining invoice rows.
Display invoice due status and day counts in one columnOpen and save Microsoft Excel XLSX filesFill the formula down for multiple invoicesUse TODAY to update the comparison date automatically
microsoft office alternative - wps office

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.