Calculate due dates in Excel: payment terms, days past due and days to collect

Every invoice carries a promise: pay within so many days. A bookkeeper turns that promise into a date, checks which invoices have passed it, and measures how long customers really take to pay. This page shows the formulas for each step, from reading "Net 30" out of a text column to skipping weekends and company holidays, and the small mistakes that make an aging report quietly wrong.

Why bookkeepers calculate due dates

Payment terms tell a customer how long they have to pay. "Net 30" means the full amount is due 30 days after the invoice date. Some businesses use Net 15, Net 45 or Net 60. Others count business days instead of calendar days, so weekends and holidays don't use up the customer's time.

The due date matters for two everyday jobs:

• Collections. Collections means following up on money customers owe. A days-past-due column tells you who to call first.
• Cash planning. A business needs to know when cash will arrive so it can pay its own bills and payroll. Due dates are the first guess; actual payment dates show how reliable it is.

Accounts receivable is the money customers owe the business. A report that lists those invoices by how late they are is called an aging report, and it starts with one question: when was each invoice due?

Why this matters for MO-220 practice

The Excel for Accounting exam (MO-220) includes preparing data for analysis, and using date functions on transaction dates is part of transforming that data [Official]. It is a 50-minute, performance-based exam, and its tasks describe the purpose instead of naming the function [Official]. A task might say "show the date each invoice must be paid, not counting weekends" and leave you to recognize WORKDAY. The exercises in this lesson are worded the same way. Accounting Exercises is not affiliated with Microsoft or Certiport.

How it works

Dates are numbers, so you can add days

The spreadsheet stores each date as a serial number: a count of days since a fixed point at the end of 1899. The date you see is a format laid over that number. Because of that, adding 30 to a date moves it 30 days forward, and subtracting one date from another gives the number of days between them.

So the basic due date is a single addition:

=B5+30

If the result shows as a five-digit number, it only needs a date format.

Reading the number out of "Net 30"

Terms are often stored as text, such as "Net 30" or "Net 45". Text can't be added to a date until you pull out the number. In "Net 30", the digits start at the 5th character: N, e, t, space, then the number. MID takes characters from the middle of a text value:

=MID(C5,5,3)

This starts at character 5 and takes up to 3 characters, which covers two-digit and three-digit terms. MID returns text, so wrap it in VALUE to get a real number, then add it to the invoice date:

=B5+VALUE(MID(C5,5,3))

In newer versions of Excel, TEXTAFTER does the same job without counting characters: =B5+VALUE(TEXTAFTER(C5," ")) takes everything after the space.

Days past due, and why the report date is locked

Days past due is the report date minus the due date. The report date is the day the report describes, usually kept in one cell at the top so the whole sheet updates when you change it. With the report date in F2:

=MAX(0,$F$2-D5)

Two details make this formula right:

• MAX(0, …). An invoice that isn't due yet gives a negative result. "Minus 8 days past due" means nothing to a collector, so MAX keeps the larger of 0 and the result. Invoices not yet due show 0.
• $F$2. The dollar signs make an absolute reference: it stays on F2 when you fill the formula down. The due date, D5, is relative, so it moves to D6, D7 and so on. Every row needs its own due date but the same report date.

Business-day terms: WORKDAY and a holiday list

When terms are in business days, adding days directly is wrong because it counts weekends. WORKDAY moves a date forward by a number of working days, skipping Saturdays and Sundays:

=WORKDAY(B5,C5,$H$5:$H$8)

The third argument is optional: a range of dates that also don't count, such as the company's holidays. Lock it with dollar signs so every row uses the same list.

WORKDAY does not count the start date itself.

Counting days between two dates

Sometimes you have both dates and need the gap. For calendar days, subtraction is enough: =D5-B5. Two functions give the same answer:

• =DAYS(D5,B5) returns the days between two dates. Note the order: end date first, start date second.
• =DATEDIF(B5,D5,"d") does the same with the start date first.

For working days, use =NETWORKDAYS(B5,D5,$H$5:$H$8). It counts the weekdays between two dates, leaves out the holidays, and includes both the start date and the end date. So it can give one more than you expect.

Days to collect, late or on time

Once an invoice is paid, the days to collect are the payment date minus the invoice date: =D5-B5. To mark it late or on time, compare those days with the terms:

=IF(E5>C5,"Late","On time")

Use greater than, not greater than or equal. Terms of 30 days mean the customer may pay on day 30. A payment on the due day is on time.

Finally, the average of the days-to-collect column gives a simple collection measure: =AVERAGE(E5:E16). If it is well above the terms, cash arrives later than the terms suggest.

Worked example 1: due dates from "Net" terms

Summit Bike Repair prepares a report dated 6/13/2026 in F2. Its invoices have dates in column B and terms as text in column C. The due date in D5 is =B5+VALUE(MID(C5,5,3)) and the days past due in E5 is =MAX(0,$F$2-D5), both filled down to row 16. Some of the rows:

Check the first row by hand. March 20 plus 45 days is May 4: 11 days to the end of March, 30 in April, and 4 in May. From May 4 to the report date of June 13 is 27 days in May plus 13 in June, which is 40 days past due.

INV-1193 is due exactly on the report date, so it shows 0: due today, not late yet. INV-1265 and INV-1642 aren't due until after the report date. Without MAX they would show -14 and -2.

Worked example 2: business days with company holidays

Copperleaf Studio gives customers a number of business days to pay. Its example company holidays in H5:H8 include 7/16/2026 and 7/27/2026. The due date in D5 is =WORKDAY(B5,C5,$H$5:$H$8), filled down. The table compares it with the two shortcuts:

Follow the first row. The invoice is dated Thursday 7/9. The five business days are Friday 7/10, Monday 7/13, Tuesday 7/14, Wednesday 7/15 and, skipping the holiday on Thursday 7/16, Friday 7/17. Adding 5 calendar days lands on 7/14, three days early. Leaving out the holidays lands on 7/16, the holiday itself. In the second row, ignoring holidays even puts the due date on 7/27, another company holiday.

Worked example 3: days to collect and late payments

Juniper Landscaping records when each invoice was paid. Days to collect in E5 is =D5-B5, the status in F5 is =IF(E5>C5,"Late","On time"), and the average in I4 is =AVERAGE(E5:E16). Some of the rows:

The invoice from 3/23 had 15-day terms and was paid after exactly 15 days, on the due day, so it is on time. Notice that 42 days is late on 30-day terms but on time on 45-day terms.

Across all twelve invoices, the average days to collect is 28.25. Five of the twelve were paid late.

Common mistakes

• An unlocked report date. Writing F2 instead of $F$2 works in the first row only. Filled down, the reference slides to F3, F4 and so on, which are empty. In the Summit Bike Repair sheet every row after the first then shows 0 days past due, so the report looks clean while invoices up to 35 days late hide in it.
• Keeping negative days. Subtracting without MAX leaves invoices that aren't due yet showing negative days, such as -14. Totals and averages of that column then come out too low.
• Calendar days instead of business days. When terms are in business days, =B5+C5 counts weekends and the due date comes out early. Use WORKDAY.
• Ignoring the holidays. WORKDAY without its holiday range skips weekends but not company holidays, so any due date whose window contains a holiday lands a day early, sometimes on the holiday itself.
• Marking the due day as late. Using >= instead of > marks a payment on the last allowed day as late. In the Juniper example that wrongly flags the invoice paid in exactly 15 days.
• Averaging the terms instead of the days. =AVERAGE(C5:C16) averages what customers were allowed, 28.75 days in the example, not what they actually took. The collection measure must average the days to collect.