Excel DAYS Function

DAYS calculates the number of days between two dates and returns that count as a plain number. It's built specifically for this one job, so it doesn't need a unit argument the way DATEDIF does. The one thing that trips people up: the arguments run in reverse order from how most people think to write them.

Available in: Excel 2013 and later, including Excel 365, Excel 2021, and Excel 2019. Not available in Excel 2010 or earlier — use DATEDIF instead on older versions.

ℹ

Array spilling with DAYS requires Excel 365 or Excel for the web. Excel 2019, 2016, and earlier versions return only a single value, not a spilled array.

Syntax

=DAYS(end_date, start_date)

Parameter	Required	Description
`end_date`	Yes	The later date, the one you're counting toward.
`start_date`	Yes	The earlier date, the one you're counting from.

⚠

DAYS asks for end_date first and start_date second. Most people instinctively type the earlier date first, which flips the calculation and returns a negative number with no error to flag it. Read the argument order twice before you build the formula.

Basic Example

You're tracking how long invoices sit unpaid. Column B has the invoice date, column C has the date payment came in.

=DAYS(C2, B2)

// C2 = payment date (the later date, so this goes first)
// B2 = invoice date (the earlier date, so this goes second)

If B2 is March 1, 2026 and C2 is March 28, 2026, DAYS returns 27. No formatting, no time component to strip out, just a whole number you can drop straight into a SUM or an average.

How DAYS Works

The argument order runs backward from how most people think

Write end_date first, start_date second. It feels wrong the first few times, since most people naturally think "start, then end." Get it backward and DAYS still calculates something, it just calculates it as a negative number instead of throwing an error.

DAYS counts whole calendar days only

Time components attached to a date are ignored entirely. =DAYS("3/15/2026 6:00 PM", "3/14/2026 8:00 AM") still returns 1, not 1.42. If you need to account for partial days or hours, subtract the date-time values directly instead of using DAYS.

DAYS handles leap years automatically

You don't add anything extra to account for February 29. =DAYS("3/1/2024", "2/28/2024") returns 2, correctly counting the leap day in between, while the same formula run against 2025 dates returns 1. Excel's date system tracks this internally, so DAYS just reads it correctly.

DAYS works with arrays, not just single cells

This is one thing most references skip. Feed DAYS a range instead of a single cell pair, and on Excel 365 it spills one result per row automatically:

=DAYS(payment_dates, invoice_dates)

If payment_dates and invoice_dates are equal-sized ranges, this single formula returns an array: one day count per row, spilling down without needing to copy the formula manually. This spilling behavior is an Excel 365 feature. On Excel 2019 or earlier, feeding DAYS two ranges instead of two single cells doesn't spill, and the result you get back can depend on exactly where the formula sits relative to the ranges rather than behaving consistently. Test it directly before relying on it, or just enter one DAYS formula per row and copy it down, which works reliably on every version.

Common Use Cases

Calculating how long a project took

You log a start date and an end date for each project and want the duration in days.

=DAYS(D2, C2)   // D2 = completion date, C2 = kickoff date

Flagging overdue accounts

You want days elapsed since an invoice was issued, compared against today.

=DAYS(TODAY(), invoice_date)   // TODAY() = current date, invoice_date = when billed

Measuring employee tenure in days

HR wants tenure in raw days rather than a fraction of a year, so it lines up with a bonus threshold.

=DAYS(TODAY(), hire_date)

Bulk-calculating durations across an order list

Instead of dragging one formula down 400 rows, spill the whole calculation at once.

=DAYS(ship_dates, order_dates)

// ship_dates  = the range of shipment dates
// order_dates = the range of order dates, same size as ship_dates

Handling Errors

DAYS throws #VALUE! when it can't recognize one of the inputs as a date, and #NUM! when a date falls outside Excel's supported range.

Common causes:

A date is stored as text in a format Excel can't parse, often because it was pasted from another system
One of the two arguments is blank or contains a non-date string
The date predates January 1, 1900, which Excel's standard date system doesn't support

=IFERROR(DAYS(C2, B2), "Check dates")

✓

DAYS is worth using even in formulas where plain subtraction would work. =C2-B2 returns the same number when both cells hold valid dates, but it silently returns a meaningless value if one cell is actually text formatted to look like a date. DAYS throws #VALUE! instead, which tells you immediately that something's wrong.

Notes & Gotchas

Can the DAYS function handle time values like hours and minutes?

No. DAYS strips out any time component and counts only full calendar days. If your dates include timestamps, =DAYS("3/15/2026 11:00 PM", "3/14/2026 1:00 AM") still returns 1, because DAYS looks only at the date portion. If you need fractional day precision, subtract the date-time values directly with =C2-B2.

Does the DAYS function account for leap years?

Yes, automatically. Excel's internal date serial numbers already account for February 29, so =DAYS("3/1/2028", "2/28/2028") correctly returns 2 in a leap year, versus 1 for the same span in 2027, a non-leap year. You never need to add leap-year logic yourself.

Does DAYS exclude weekends and holidays?

No. DAYS counts every calendar day between the two dates, weekends and holidays included. If you need working days only, use NETWORKDAYS instead, which is built specifically to skip Saturdays and Sundays and can exclude a holiday list too.

Why use DAYS instead of just subtracting two dates?

Subtraction (=C2-B2) gives the same result when both cells hold true dates, but it fails quietly if one of them is text. DAYS is more explicit about what it's doing, and it throws a catchable #VALUE! error instead of returning a number that looks plausible but isn't. That distinction matters most in workbooks other people will edit later.

Why does DAYS return a negative number, and how do I fix it?

A negative result almost always means the arguments are reversed: start_date is actually later than end_date. Check which date belongs in which argument slot; remember DAYS wants end_date first. Swap them and the result flips to positive.

What happens if my dates are entered as text in different regional formats?

DAYS depends on Excel correctly interpreting each argument as a date, and text dates are where regional formatting causes real damage. "5/6/2026" reads as May 6 in the US but June 5 in much of Europe, and DAYS will calculate against whichever interpretation your system locale applies, with no warning that it guessed. Convert text entries to real dates with DATEVALUE, or format the source column as text-with-dashes ("2026-05-06") to remove the ambiguity entirely.

Does DAYS work on a whole column of date pairs at once?

Yes, on Excel 365. Point DAYS at two equal-length ranges instead of two cells, and it spills one result per row without an array formula or a fill-down. This works well for order logs or shipment trackers where you'd otherwise copy the same formula down hundreds of rows. On Excel 2019 or earlier, the same formula doesn't spill and can behave inconsistently depending on where it's entered, so copy a single-row DAYS formula down instead.

Related Functions

Function	Use this when...
`DATEDIF`	You need the difference between two dates in months or years, not just days.
`DATE`	You need to build a date from separate year, month, and day values before running DAYS on it.