calculate invoice

miciah paige 40 Reputation points
2026-11-18T14:45:54.3233333+00:00

Hi everyone,

I'm creating an invoice tracker in Excel and would appreciate some advice. For example, invoice INV-1001 was issued on 10/01/2026 and was due on 10/15/2026, while INV-1002 was issued on 10/05/2026 and was due on 10/19/2026. Both are currently marked as "Unpaid". I also have other invoices with a "Paid" status.

I'd like Excel to automatically calculate the number of overdue days based on today's date, while displaying 0 for invoices that have already been paid.

Could anyone suggest a suitable formula for this? Thank you in advance for your help!

Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments

Answer accepted by question author
Jayden-P 4,465 Reputation points Independent Advisor
2026-11-18T14:54:11.8966667+00:00

Hi @miciah paige

The AI answer is on the right track, you can give it a check.

Assuming:

  • Due Date is in C2
  • Status is in D2 ("Paid" or "Unpaid")

Use:

=IF(D2="Paid",0,MAX(0,TODAY()-C2))

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

Answer accepted by question author

AI answer

2026-11-18T14:46:11.85+00:00

If the due date is in C2 and the payment status is in D2, use:

=IF(D2="Paid",0,TODAY()-C2)

This returns:

  • 0 when the invoice is marked Paid
  • the number of days past the due date when it is not paid

If only overdue invoices should show a value, and invoices that are not yet due should also show 0, use:

=IF(D2="Paid",0,MAX(0,TODAY()-C2))

Example:

  • INV-1001 due 10/15/2026 and Unpaid → returns the number of days from 10/15/2026 to today
  • INV-1002 due 10/19/2026 and Unpaid → returns the number of days from 10/19/2026 to today
  • any invoice marked Paid → returns 0

If the status uses Y/N instead of Paid/Unpaid, adapt it like this:

=IF(D2="Y",0,MAX(0,TODAY()-C2))

Excel stores dates as serial numbers, so subtracting the due date from TODAY() gives the day difference directly.


References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.