Date math in spreadsheets

TL;DR:

• A spreadsheet stores a date as a number of days, so subtracting two dates gives the days between them.

• NETWORKDAYS counts working days, includes both end dates, and skips weekends and any holidays you list.

• If a date will not calculate, it is probably text. Convert it to a real date first.

How many working days until the report is due? How long did the ticket stay open? Spreadsheets are very good at this, but only once you know that a date is really a number. After that, date math is just subtraction.

Dates are numbers in costume

Excel and Google Sheets store a date as a count of days from a starting date. The cell shows it in a date format, but underneath it is a number, and the format only controls the look. That is why you can subtract one date from another and get a plain number of days, and why adding 7 to a date moves it one week forward.

You can see this for yourself. Type a date in a cell, then change its format to Number. The date turns into a number, and changing the format back restores the date.

Before you start

• Make sure your dates are real dates. A real date is usually right-aligned by default and changes when you change its format. Text is left-aligned and does not.

• Check the order your app expects for day and month (month first or day first). It depends on your regional settings, and a wrong guess swaps days and months silently.

• Use DATE(2026, 11, 13) to write a date in a formula, because it cannot be misread.

• Format the result cell as a number, not a date. Subtracting dates sometimes gets a date format applied automatically and shows a strange date instead of 11.

Calendar days between two dates

Subtract the earlier date from the later one: =B2-A2. If the start is 2 November 2026 and the end is 13 November 2026, the result is 11. This counts the days from the start to the end, so it is the number of nights between them, and does not include the start day itself.

Adding days works the same way: =A2+30 is thirty days later. For months, use EDATE(A2, 1), which moves one calendar month and handles different month lengths.

Working days with NETWORKDAYS

Often what you want is working days. NETWORKDAYS takes a start date, an end date and an optional list of holidays.

=NETWORKDAYS(start_date, end_date, [holidays])

It counts Monday to Friday, and it includes both the start and the end date when they are working days. That differs from simple subtraction, which does not include the start. It also skips any dates in the holiday range you give it.

If your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL, which lets you say which days are the weekend. Both Excel and Google Sheets have it.

Worked example: working days between two dates

A task starts on Monday 2 November 2026 and is due on Friday 13 November 2026. Your office is closed on Wednesday 11 November.

1. Put the dates in cells: A2 holds =DATE(2026,11,2), B2 holds =DATE(2026,11,13), and the holiday goes in F2 as =DATE(2026,11,11).

2. Subtract for calendar days: =B2-A2 gives 13 − 2 = 11.

3. Count working days without the holiday: =NETWORKDAYS(A2,B2). The two weeks are Monday 2 to Friday 6 (5 working days) and Monday 9 to Friday 13 (5 working days), so the answer is 10.

4. Add the holiday list: =NETWORKDAYS(A2,B2,F2:F2) removes 11 November, so it returns 10 − 1 = 9.

To check it against a calendar: week one has the 2nd, 3rd, 4th, 5th and 6th, week two has the 9th, 10th, 11th, 12th and 13th. That is 10 weekdays, and taking out the 11th leaves 9. The weekends of the 7th and 8th are not counted either way.

Notice that 11 calendar days and 10 working days are different because of counting rules and weekends, not because of a mistake. Decide up front whether your deadline counts the start day, and state it.

The holiday list

Keep holidays in a column on a separate tab, one real date per cell, and refer to the whole range, for example Lists!A2:A20. You then update the list once a year and every formula follows. Do not type holidays as text, because text dates are silently ignored.

Do not guess holiday dates. Take them from your company calendar or an official source for your country, since they differ by place and by year.

Mistakes to avoid

• Text dates. If =B2-A2 gives #VALUE!, one of them is probably text. Try DATEVALUE, or re-enter the dates.

• Swapped day and month. 03/04/2026 can mean 3 April or 4 March. Test with an unambiguous date such as 13 November, which can only be day 13, and see that it displays correctly.

• A result shown as a date. Set the result cell's format to Number.

• Off by one. NETWORKDAYS counts both ends, subtraction does not. Know which one your question needs.

• Wrong order. If the start is after the end, NETWORKDAYS returns a negative number.

Doing it faster

When you set up a due-date column, build it once and copy the formulas. If a formula on your screen misbehaves and you cannot see why, press DuckyHelper's key and ask out loud while it looks at the sheet. Most date problems come down to the same two causes: a text date, or a counting rule that was not agreed.

Frequently asked questions

Why does subtracting two dates sometimes show a date?

The cell inherited a date format. Change the result cell's format to Number.

Does NETWORKDAYS include the start day?

Yes, if it is a working day. The end day is also counted.