How to Calculate a Late Fee with IF and Dates in Excel

Compare due dates with paid dates to count days late, charge a fee with a minimum, and report status against an as-of date.

You will learn to

• Count the days an invoice was paid late
• Charge a percentage fee with a minimum
• Report status for paid and unpaid invoices using an as-of date

How it works

Dates in Excel are numbers, so you can compare and subtract them directly. =IF(C2>B2,C2-B2,0) gives the days an invoice was paid after its due date, and 0 if it was on time. A fee rule such as "2% of the amount, minimum $25" becomes =IF(C5>B5,MAX(25,D5*0.02),0).

For unpaid invoices, compare the due date with an as-of date typed in a cell rather than TODAY(). The report then stays reproducible — the same numbers every time you open it — and you can change one cell to see the position on any date.

=IF(C5>B5,MAX(25,D5*0.02),0)