Excel date formulas: due dates, delays, working days

In Excel, a date is a number. 1 January 1900 is 1, and each day adds 1: 4 November 2026 is 46330. That is what makes calculations possible. A due date at 30 days is the start date plus 30. A delay is today minus the due date.

The difficulties start with weekends, public holidays, month ends and length of service. This guide gives the formulas for each case, with examples worked out on real dates, and the pitfalls that skew results. It complements our guide to the Excel dashboard, where these dates feed the indicators.

How does Excel store dates?

Excel stores each date as a serial number. According to Microsoft's documentation, January 1, 1900 is serial number 1 in Excel for Windows' default date system. The cell format only changes the display: 46330 and 4/11/2026 are the same value.

Three practical consequences:

• A date typed as text does not calculate. If "04/11/2026" is pasted from another program and stays left-aligned, Excel sees it as text. =DATEVALUE(A2) converts it, or use Text to Columns on the Data tab.
• A gap between two dates sometimes shows as a date. =B2-A2 returns a number of days; if the cell inherits the Date format, 14 shows as "14/01/1900". Switch it to Number.
• A time is a fraction of a day. Noon is 0.5. A cell with date and time gives decimal gaps: =INT(B2-A2) keeps whole days.

The 12 date functions to know

Twelve functions cover almost every small-business need: building a date, shifting it, counting working days, measuring a gap. The names below are those of English Excel; arguments are separated by commas.

Examples worked out with A2 = 04/11/2026 and B2 = 30/11/2026 (except DATEDIF: A2 = 15/03/2019, B2 = 04/11/2026), and an empty Holidays range: there is no bank holiday in England and Wales in November.

How do you calculate a due date?

For a due date in calendar days, add the delay to the start date: =A2+30. An invoice issued on 15 November 2026 at 30 days is due on 15 December 2026. For a due date in months, at month end or in working days, three other formulas apply.

EDATE has a behaviour worth knowing: when the day does not exist in the target month, Excel takes the last day of the month. =EDATE(DATE(2027,1,31),1) returns 28 February 2027, and one month later 28 March, not the 31st. For monthly due dates that always fall at month end, prefer EOMONTH, which does not drift.

How do you calculate a delay?

The delay is the gap between today and the due date, once the due date has passed: =MAX(0,TODAY()-B2). The formula returns 0 until the date is passed, then the number of days late. With a due date of 15 October 2026, the delay on 4 November 2026 is 20 days.

For a tracking sheet, two more columns make the delay readable:

• A status: =IF(B2="","No due date",IF(B2<TODAY(),"Late",IF(B2-TODAY()<=7,"This week","Upcoming"))).
• Conditional formatting: a rule "Use a formula: =AND($B2<>"",$B2<TODAY())", light red fill, applied to the whole row.

TODAY is a "volatile" function: it recalculates on every opening and every change to the workbook. The delay is therefore always current, but it changes every day. If you need to freeze a date (a reminder sent, for example), type it with Ctrl+; instead of calculating it.

How do you count working days, holidays included?

NETWORKDAYS counts Monday-to-Friday days between two dates, both ends included, minus the holidays you give it. From 2 to 30 November 2026, that is 21 working days. WORKDAY does the reverse: it returns the date after n working days.

The third argument, the holiday list, is optional. It is the one most often forgotten, and the most costly. According to GOV.UK, England and Wales have 8 bank holidays in 2027. When one falls on a weekend, a substitute weekday is given.

In 2027, all 8 bank holidays in England and Wales fall on weekdays, two of them as substitute days. Without a holiday list, a working-day count over the year is therefore 8 days off. Here is the list to paste into a "Holidays" sheet and name Holidays (Formulas › Define Name):

Scotland and Northern Ireland have their own lists, published on the same GOV.UK page. Elsewhere, use your country's official calendar.

If your working week is not Monday to Friday, use the .INTL versions. According to Microsoft, code 11 means a weekend of Sunday only, and a seven-character string describes any week: "0000011" has Saturday and Sunday off, "0000001" Sunday only. A shop open Tuesday to Saturday is written "1000001".

How do you calculate length of service or age?

DATEDIF calculates the gap between two dates in full years, months or days. For someone hired on 15 March 2019, on 4 November 2026, =DATEDIF(A2,B2,"Y") returns 7 years, and =DATEDIF(A2,B2,"YM") the remaining 7 months.

Two pitfalls. According to Microsoft's documentation, the "MD" unit may result in a negative number, a zero or an inaccurate result: Microsoft advises against it. And if the start date is later than the end date, DATEDIF returns #NUM!. For length of service in plain words, combine: =DATEDIF(A2,B2,"Y")&" years and "&DATEDIF(A2,B2,"YM")&" months".

Which common mistakes should you avoid?

Most date errors come not from formulas but from data: dates typed as text, mixed formats, forgotten holidays. Five come up often:

1. Dates as text after a copy-paste: left-aligned, they do not sort and do not calculate.
2. US versus UK format: 11/04/2026 read as 4 November or 11 April depending on regional settings. Prefer typing 2026-11-04, understood everywhere.
3. No holiday list in WORKDAY and NETWORKDAYS.
4. A TODAY formula where a frozen date is needed: the reminder date "moves" every day.
5. The "MD" unit of DATEDIF, which gives wrong results at month end.

And when the file is no longer enough?

A formula calculates a due date, but it tells nobody. The sheet shows "Late" in red, provided someone opens the file. And as soon as several people edit it, dates typed as text and overwritten formulas come back. It is the same limit described in our guide to inventory management in Excel.

The real question is often not "which formula?" but "who needs to be told, and when?". A useful due date triggers an action: an e-mail to the client three days before, an alert to the manager on the day.

In Rangee, a Date field refuses text, and formulas do the same calculations: WORKDAY({Start}, 10, holidays) for a date in working days, WORKDAY_DIFF to count them, DATETIME_DIFF for a gap, IS_BEFORE({Due date}, TODAY()) to spot a delay. A scheduled automation can then send an e-mail every morning for the day's due dates. Our comparison of European Airtable alternatives places Rangee among other no-code databases.

If you want to turn a file of due dates into a tool that sends its own reminders, join the Rangee beta: the Free plan never expires, and readers never pay.