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: