How to Calculate Days Between Two Dates in Excel, and Fix DATEDIF #NUM!

Count days, working days and months between two dates in Excel, then fix a result shown as a date, #VALUE! from text dates and DATEDIF's #NUM!.

Founder, Glow Reports. , 4 min read
  • Excel
  • Formulas
  • Reporting Workflows
Two calendar pages joined by a measuring tape, with weekend days shaded and an invoice beside them
On this page
  1. How do you calculate the days between two dates in Excel?
  2. Why does subtracting two dates show a date instead of a number?
  3. Why do I get #VALUE! when I subtract dates?
  4. Why does DATEDIF return #NUM!?
  5. How do you count working days between two dates?
  6. How do you calculate months or years between two dates?
  7. What if the dates include a time?
  8. How do you report average days to pay or to ship?
  9. Does this work in Google Sheets?
  10. Where does Glow Reports fit?
  11. Common questions

An invoice export has an issue date and a payment date. An order export has an order date and a ship date. You want the number of days between the two, and Excel hands you a date in January 1900, a #VALUE! error, or DATEDIF's #NUM!. The fix is short: subtract the earlier date from the later one, format the result as a number, and use NETWORKDAYS when only working days count. This guide covers each formula, then each error and how to clear it, on a small invoice export you can rebuild in two minutes.

How do you calculate the days between two dates in Excel?

Subtract the earlier date from the later one: , with the start date in B2 and the end date in C2. Excel stores each date as a number of days, so the difference is a number of days. If the result looks like a date, set the cell format to Number or General.

Microsoft gives the same advice on its DATEDIF page: to count days, subtract one date from the other, because dates are stored as numbers Microsoft Support. By default, 1 January 1900 is serial number 1. 27 March 2026 is 46108 and 6 April 2026 is 46118, so the subtraction returns 10.

The DAYS function does the same with the end date first: . It differs in one useful way. If a date argument is text, DAYS reads it with DATEVALUE and returns #VALUE! when the text can't be read as a date Microsoft Support. Pick one form and keep it for the whole column.

Here is the sample export used in the rest of this guide. Type it in A1:C6 to follow along.

InvoiceIssuedPaidWorking days, French holidays
INV-10127/03/202606/04/2026106
INV-10201/04/202630/04/20262921
INV-10315/04/202615/05/20263020
INV-10430/04/202611/05/2026116
INV-10504/05/2026(unpaid)

Why does subtracting two dates show a date instead of a number?

Because the result cell carries a date format. Excel often copies the format of the dates into the formula cell, so 10 days shows as 10/01/1900, the tenth day of Excel's calendar. The number underneath is right. Change the format with HomeNumber FormatGeneral.

You can confirm it with , which returns TRUE. If you see ##### instead, the column is too narrow or the result is negative and formatted as a date. Excel can't show a negative date, so widen the column and switch it to General to see the real value.

Why do I get #VALUE! when I subtract dates?

One of the two cells holds text that Excel can't read as a date. Exports often write dates as , in another country's day and month order, or with a trailing space. Arithmetic on text that isn't a recognizable date returns #VALUE!.

Test each column with . FALSE means text. Fix the column once, then subtract. The text to date guide has the formula for each export format, from ISO timestamps to Unix time.

Watch for the silent version of this problem. A US date such as 03/04/2026 opened on a computer set to UK or French dates becomes 3 April instead of 4 March, with no error. The subtraction then returns a number that is wrong by weeks. When a delay looks off, check one row by hand.

Why does DATEDIF return #NUM!?

DATEDIF returns #NUM! when the start date is later than the end date Microsoft Support. In an export that means the two columns are in the wrong order in your formula, the dates were swapped on import, or the end date is blank, which Excel reads as 0, the day before 1 January 1900.

Check the order first. The syntax is , start first. A plain subtraction gives a negative number in the same situation instead of an error.

Then look at blanks. An unpaid invoice has no payment date, so returns minus 46146 and returns #NUM!. Both break an average. Leave open rows empty instead:

Days to pay, blank while unpaid
=IF(C2="","",C2-B2)

AVERAGE skips the empty text that IF returns, so gives 20 days for the four paid invoices. To count the days an open invoice has been waiting so far, use for the rows where C is blank.

Don't hide #NUM! with IFERROR or by wrapping the dates in MIN and MAX. A payment recorded before its invoice is a data problem, and the error is how you find it. Filter the column on the error and fix the source rows.

How do you count working days between two dates?

Use NETWORKDAYS: , with a list of public holidays in H2:H5. It returns the whole working days between the two dates, leaving out Saturdays, Sundays and every date in the holiday list Microsoft Support.

NETWORKDAYS counts both the start day and the end day when they are working days. Microsoft's own example, 1 October 2012 to 1 March 2013, returns 110, which includes both ends. For a delay, where an invoice paid the day it was issued should count 0, subtract 1:

Working days to pay, 5 for INV-101
=NETWORKDAYS(B2,C2,$H$2:$H$5)-1

In the sample, the holiday list holds the French public holidays of the period: 6 April 2026 (Easter Monday), 1 May, 8 May and 14 May 2026 (Ascension), as listed by the French administration Service Public. INV-101 runs from Friday 27 March to Monday 6 April. Without the holiday list, NETWORKDAYS counts 7 days. With it, Easter Monday drops out and the count falls to 6. The last column of the table above uses the holiday list.

Enter the holiday dates as real dates, typed in cells or built with DATE. Microsoft warns that holidays entered as text cause problems, and that any argument that isn't a valid date returns #VALUE!.

If your weekend isn't Saturday and Sunday, NETWORKDAYS.INTL takes a weekend code. Code 7 means Friday and Saturday, and code 11 means Sunday only. A seven-character string such as sets any pattern, starting with Monday Microsoft Support.

How do you calculate months or years between two dates?

Use DATEDIF with the unit "m" for complete months or "y" for complete years: . It counts only whole periods, so 31 January to 28 February returns 0 months, because the month isn't complete. For a fraction of a year, use YEARFRAC instead.

The units Microsoft lists are "y", "m", "d", "md", "ym" and "yd". Microsoft also warns that DATEDIF exists to support workbooks from Lotus 1-2-3 and may calculate incorrect results in some cases. It advises against the "md" unit, which can return a negative number, a zero or a wrong result Microsoft Support.

For reports, three uses cover most needs:

  • Customer tenure in months: with the signup date in B2.
  • Age of an account in years: .
  • Years as a decimal, such as 1.5: . YEARFRAC returns the fraction of a year between two dates, with an optional basis for the day count Microsoft Support.

What if the dates include a time?

Then the subtraction returns days with a decimal part. An order placed on 2 March at 18:00 and shipped on 4 March at 09:00 gives 1.625 days. That is right for an elapsed time, and wrong if you want calendar days.

For calendar days, drop the time from both sides first: returns 2 for the same order. For hours, multiply the difference by 24: returns 39. Decide which one your report means, write it in the column header, and keep it the same every month.

How do you report average days to pay or to ship?

Add a column with the delay for each row, then summarize it. gives the mean, the middle value, and the average for one customer. Report the median next to the mean, because a few late payers pull the mean up.

To show the delay by month, add a month column with and use a PivotTable with the month in Rows and the average of the delay in Values. The pivot table by month guide shows how to group real dates without a helper column. For a delay with conditions, such as the average for one month and one customer, the rules are the same as in SUMIFS with multiple criteria.

Keep the blank rows out of the average, as in the IF formula above, and say in the report how many rows are still open. An average of 20 days to pay means little if a third of the invoices haven't been paid yet.

Does this work in Google Sheets?

Yes. Subtraction, DAYS, DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL and YEARFRAC exist in Google Sheets with the same arguments. DATEDIF accepts the same units, and Google documents "MD" as the days left after subtracting whole months Google Docs Editors Help.

NETWORKDAYS in Sheets also takes a holiday range or array Google Docs Editors Help. If a result shows as a date, change the format with FormatNumberNumber. Text dates fail in Sheets too, and Sheets reads them according to the spreadsheet's locale.

Where does Glow Reports fit?

The formulas above work well on one export. The work grows when the report needs days to pay by customer, by month and by invoice size, across several files, every month.

With Glow Reports, you upload the CSV or Excel files and ask in plain language, such as the median days to pay by month, with unpaid invoices left out. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check how the delay was computed and which rows were excluded, and edit the query if your definition differs. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the formula for days between two dates in Excel?

, with the later date in C2 and the earlier date in B2. Format the result as a number. gives the same result with the end date first.

Why is DATEDIF not working in Excel?

It returns #NUM! when the start date is after the end date, often because the arguments are in the wrong order or the end date is blank. It returns #VALUE! when a cell holds text that isn't a date. Check both columns with ISNUMBER.

Does NETWORKDAYS include the start date?

Yes. NETWORKDAYS counts the start and end dates when they are working days. Subtract 1 to get a delay, so that a task finished the day it started counts 0.

How do I count days between a date and today?

Use with the date in B2. TODAY updates each time the workbook recalculates, so the result changes from one day to the next. Paste it as a value if you need a fixed figure for a report.

Can I calculate days between dates without DATEDIF?

Yes. Subtraction or DAYS gives days, NETWORKDAYS gives working days, and YEARFRAC gives years as a decimal. DATEDIF is only needed for complete months or years.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your invoice or order exports to Glow Reports and ask for days to pay by month, with the SQL behind each figure.

Keep reading