# How to calculate a late fee based on days overdue in Excel

Charge the daily fee for each day late, up to a cap: =IF(C2>B2,MIN($G$2,(C2-B2)*$G$1),0).

**Excel:** `=IF(C2>B2,MIN($G$2,(C2-B2)*$G$1),0)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Rental | Due back | Returned | Late fee |  | Fee per day | 2.5 |
| R-1 | 2026-10-10 | 2026-10-09 |  |  | Maximum fee | 20 |
| R-2 | 2026-10-12 | 2026-10-15 |  |  |  |  |
| R-3 | 2026-10-14 | 2026-10-14 |  |  |  |  |
| R-4 | 2026-10-15 | 2026-10-28 |  |  |  |  |
| R-5 | 2026-10-18 | 2026-10-20 |  |  |  |  |

Formula in D2, filled to D6: `=IF(C2>B2,MIN($G$2,(C2-B2)*$G$1),0)`

Result: $0.00; $7.50; $0.00; $20.00; $5.00

## How it works

Subtracting the due date from the return date gives the days late. When that is positive, the fee is days × daily fee, and MIN keeps it at or below the cap: R-4 is 13 days late, which would be 32.50, so it is charged 20.

## Other ways

- `=MIN($G$2,MAX(0,C2-B2)*$G$1)`: No IF: MAX(0,…) turns early returns into 0 days, and MIN applies the cap.
- `=IF(C2>B2,(C2-B2)*$G$1,0)`: Without a cap. (R-4 is charged 32.50 instead of the 20 maximum.)

## Common mistakes

- Items not yet returned have no return date and show 0. Charge them up to today with `IF(C2="",TODAY(),C2)` in place of C2.
- Format the fee column as currency. If it picks up a date format from the date columns, 7.5 shows as a date.

## Questions

**How do I allow a 2-day grace period?**

Subtract it from the days late: `=MIN($G$2,MAX(0,C2-B2-2)*$G$1)`.

**How do I count only working days late?**

Use NETWORKDAYS: `=MAX(0,NETWORKDAYS(B2,C2)-1)*$G$1`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/late-fee-based-on-days-overdue