Excel Formula for Overdue Days: Dates and Examples

Formulas & functions · Updated November 18, 2026

To calculate overdue days in Excel, put the due date in A2 and use =IF(A2="","",MAX(0,TODAY()-A2)) in another cell. It returns 0 when the item is not overdue, the number of calendar days late when it is, and a blank result when A2 is empty.

What the overdue-days formula calculates

Excel stores dates as numbers, so subtracting one date from another gives the number of days between them. In the formula above, TODAY() supplies the current date and A2 supplies the due date. If the due date was three days ago, the subtraction returns 3; if it is today, the result is 0. MAX(0,…) prevents a future due date from producing a negative number.

The IF check is there to handle an empty due-date cell. Without it, Excel treats a blank cell like zero in date arithmetic, which can produce a very large and misleading overdue count. The formula returns an empty string instead, so the row looks empty until a due date is entered.

This is a calendar-day calculation. It counts weekends and holidays because a calendar day is simply the difference between two dates. That is usually suitable for invoices, subscriptions, and general deadline tracking when the question is how many dates have passed since the deadline.

Set up the worksheet with real dates

Use one column for due dates and another for the result. For example, place a heading such as Due Date in A1 and Overdue Days in B1, enter each due date in A2:A20, then put the formula in B2 and fill it down. In Excel for Microsoft 365 on Windows, drag the fill handle at the bottom-right corner of B2, or copy B2 and paste into the remaining cells. Excel adjusts A2 to A3, A4, and so on.

Excel must recognize the due date as a date value, not merely text that looks like a date. A quick check is to select the cell and change its number format: a real date will display differently when formatted as General or Number. You can set a date format from Home > Number Format. If dates imported from another system are stored as text, subtraction may return a #VALUE! error; convert them to actual dates before relying on the overdue calculation.

Be mindful of regional date conventions. For example, 04/05/2026 can mean April 5 or May 4, depending on the workbook’s locale and how the value was entered or imported. Use an unambiguous entry method, check the displayed date, and confirm a few imported records against the source data.

Choose how to display dates that are not overdue

Some trackers should show zero for an item that is not late; others look cleaner with a blank. The first formula uses MAX to show 0 for dates due today or in the future. If you prefer a blank until the day after the deadline, use an IF test instead.

With due dates in A2:A20, put this formula in B2 to return a blank when the due date has not passed and a day count when it has:

=IF(A2="","",IF(TODAY()>A2,TODAY()-A2,""))

For a due date of yesterday, this returns 1. For a due date of today, it returns blank because TODAY() is not greater than the due date. Decide which behavior is more useful for your report: zero makes non-overdue status explicit and easier to total, while blank can make a list less visually busy. Both approaches count the day after the due date as the first overdue day.

If a date cell can include a time as well as a date, the time may affect the subtraction. For example, a due date stored as today at 4 p.m. is numerically later than TODAY(), which represents midnight at the start of today. For date-only reporting, remove the time component with INT.

With a date or date-time due value in A2, use this formula in B2 to ignore its time component:

=IF(A2="","",MAX(0,TODAY()-INT(A2)))

INT rounds a positive Excel date-time serial down to its whole-number date. This makes the result consistent for a tracker that treats the deadline as a calendar date rather than a specific time.

Stop counting when an item is paid or completed

An overdue count should usually stop once the invoice is paid or the task is completed. One simple layout has Due Date in A, Status in B, and Overdue Days in C. If the status is exactly Paid, show a blank; otherwise, calculate the overdue count.

With the due date in A2 and the status in B2, enter this in C2 and fill down:

=IF(OR(A2="",B2="Paid"),"",MAX(0,TODAY()-A2))

Excel’s text comparison here is not case-sensitive, so Paid and paid match. The formula does require the status to match the word Paid; a status such as Settled or Complete will not stop the count unless you change the test to match the terms used in your sheet. Consistent dropdown values help prevent spelling variations from undermining the result.

If you record a payment date rather than a status, a date in the payment column can indicate that the count should stop. With the due date in A2, payment date in B2, and result in C2, use:

=IF(A2="","",IF(B2<>"","",MAX(0,TODAY()-A2)))

This leaves the result blank once B2 contains anything. If you want to retain a final count as of the payment date instead, calculate the difference between the due date and payment date for paid records, and use TODAY() only for unpaid records. That distinction matters when the worksheet is also used as a historical record: a live overdue count changes over time, whereas a count based on the payment date stays fixed.

Use a business-day count when weekends should not count

Calendar days and business days answer different questions. For a workday count that excludes Saturday and Sunday, Excel provides NETWORKDAYS. The formula below starts counting the day after the due date, so a deadline due Monday is one business day overdue on Tuesday. It can also exclude holidays listed in a worksheet range.

With due dates in A2:A20 and holiday dates in H2:H20, use this formula in B2:

=IF(A2="","",MAX(0,NETWORKDAYS(A2+1,TODAY(),$H$2:$H$20)))

The +1 makes the first counted date the day after the deadline. NETWORKDAYS includes both its start and end dates when they are working days, so the formula counts eligible weekdays from that first date through today. MAX keeps a future due date from producing a negative result. If you do not maintain a holiday list, the holiday argument can be omitted; then only Saturday and Sunday are excluded.

Use actual Excel dates in the holiday range, not text labels. For a date-time due value, use INT(A2)+1 as the start-date argument so the time does not affect the business-day boundary. Also note that NETWORKDAYS assumes the standard Saturday-Sunday weekend. If your organization has a different workweek, NETWORKDAYS.INTL supports alternate weekend patterns; confirm the weekend code or mask against Excel’s function help before applying it to a business process.

Calculate overdue days as of a chosen date

TODAY() makes a tracker update as the current date changes. That is helpful for a live list, but it can make an old report show a different count later. A dedicated “As of” date cell lets you reproduce a snapshot, test a future date, or report all records against the same cutoff.

For this layout, put due dates in A2:A20, enter the report date in C1, and put the result formula in B2:

=IF(A2="","",MAX(0,$C$1-A2))

The dollar signs lock C1 when you fill the formula down. If C1 contains April 10 and a due date in A2 is April 7 of the same year, the result is 3. Changing C1 changes the report’s reference date without changing the due dates. Format C1 as a date and label it clearly so a reader knows what date the calculation represents.

For a business-day snapshot, use the same reference cell as the end date. With due dates in A2:A20, the as-of date in C1, and holidays in H2:H20, enter this in B2:

=IF(A2="","",MAX(0,NETWORKDAYS(A2+1,$C$1,$H$2:$H$20)))

Format and check the result

The result is a count, not a date. If Excel displays a strange date instead of a number, select the result cells and choose Home > Number Format > General or Number. A date format tells Excel to interpret the returned number as a date serial for display; it does not mean the subtraction itself is wrong.

Check a few known cases before copying a formula through a large tracker:

  • A due date equal to today should return 0 in the MAX version.
  • A due date from yesterday should return 1 calendar day.
  • A due date in the future should return 0, not a negative number.
  • An empty due-date cell should remain blank.
  • A paid or completed record should follow the rule you chose for closed items.

TODAY() is a volatile function: its value is recalculated when Excel recalculates the workbook. It is not a timestamp permanently stored when the row is created. Excel usually uses Automatic calculation, but a workbook set to Manual calculation may not update until recalculation. On Windows, F9 recalculates formulas; on a Mac, use Fn+F9 on keyboards where function keys control system features, or use the Formulas tab’s calculation command. The exact key behavior can depend on the keyboard settings.

For a visual warning, you can use conditional formatting rather than changing the formula. Select the overdue-day result cells and choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, then enter 0 and choose a format. That highlights positive counts, while zero and blank results remain unhighlighted. Conditional formatting changes appearance only; it does not alter the calculation.

Common problems and what they mean

  • A very large count for an empty row: the formula probably subtracts a blank cell without checking for it. Add an IF blank check.
  • #VALUE!: one of the cells may contain text rather than a date, or an imported date may not have converted correctly. Convert the source value to a real Excel date.
  • A negative result: the formula does not cap future dates at zero. Wrap the subtraction in MAX(0,…), if zero is the intended display.
  • The count seems off by one: decide whether the due date itself should count. The examples here treat the first overdue day as the day after the due date.
  • Paid items keep accumulating days: the formula may not be checking the payment date or status, or the status text may not match the formula’s test.
  • The number looks like a calendar date: change the result cells’ number format to General or Number.

Another common misconception is that a formula knows whether a deadline is officially overdue. It only applies the rule encoded in the cells. If a contract, policy, or workflow grants a grace period, uses a cutoff time, or defines business days differently, encode that rule deliberately and verify it with the relevant source. A basic date subtraction does not make those policy decisions.

Frequently Asked Questions

Does Excel count the due date as an overdue day?
Not in the formulas above. A due date of today returns 0; the next calendar day returns 1. If your process counts the due date itself, that is a different rule and should be stated clearly in the worksheet.

How do I calculate overdue days without counting weekends?
Use NETWORKDAYS with the start date set to the day after the due date, as in the business-day example above. Supply a range of holiday dates if holidays should be excluded too.

Why does TODAY() not seem to update?
TODAY() updates when Excel recalculates the workbook. Check that calculation is not set to Manual, then recalculate. A formula using TODAY() is a live calculation, not a saved record of the date it was first entered.

Can I show overdue days only for unpaid invoices?
Yes. Add a status or payment-date condition so closed invoices return blank, as shown above. Make sure the formula checks the exact status value used in your data.

How can I keep an overdue report from changing later?
Use an as-of date in a cell and subtract the due date from that fixed reference instead of using TODAY(). Keep the chosen report date with the worksheet so readers can interpret the results.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.