How to Calculate Date Duration in Excel: A Simple Guide

Ever stared at a spreadsheet and wondered how many days, weeks, or months have passed between two dates? Excel has a few built‑in tricks that make this surprisingly painless—once you know where to look. Below you’ll find the most common methods, along with a couple of handy shortcuts for those “just‑in‑time” calculations.

Understanding Excel’s Date System

Excel stores dates as serial numbers, counting days from January 1 1900 (or 1904 on Mac). That means January 1 2024 is actually 45245 under the hood. When you subtract one date from another, Excel simply subtracts those numbers, giving you the raw day count.

Because of this, the basic formula =EndDate‑StartDate already returns the number of days between the two cells—no extra functions required.

Basic Day Difference

Here’s the most straightforward approach:

• Enter your start date in A2 and end date in B2.
• In C2, type =B2‑A2.
• Press Enter and you’ll see the total days.

If you want the result as a positive number regardless of order, wrap the subtraction in ABS:

=ABS(B2‑A2)

Excluding Weekends

Most projects care only about business days. Excel’s NETWORKDAYS function does the heavy lifting:

=NETWORKDAYS(A2,B2)

That returns the count of Monday‑through‑Friday days, automatically ignoring Saturdays and Sundays. Want to add custom holidays? Just add a range of holiday dates as the third argument:

=NETWORKDAYS(A2,B2,$E$2:$E$5)

Counting Full Months or Years

When you need a higher‑level view—say, “how many complete months between these dates?”—use DATEDIF. It’s a legacy function that isn’t listed in the formula bar, but it still works perfectly:

=DATEDIF(A2,B2,"M")   ' full months
=DATEDIF(A2,B2,"Y")   ' full years
=DATEDIF(A2,B2,"MD")  ' remaining days after months

Mixing units is easy. For example, to express the gap as “X years, Y months, Z days”:

=DATEDIF(A2,B2,"Y") & " years, " &
DATEDIF(A2,B2,"YM") & " months, " &
DATEDIF(A2,B2,"MD") & " days"

Dynamic Duration with TODAY()

Often you need the interval from a past event up to today. Pair the previous formulas with TODAY():

=TODAY()‑A2          ' days elapsed
=NETWORKDAYS(A2,TODAY())  ' business days elapsed

This automatically updates each day, so you never have to refresh the sheet manually.

Formatting Tips

Excel sometimes treats the result as a date, which can be confusing. After you enter a duration formula, simply set the cell’s format to Number (or General) to see a plain integer.

If you prefer a friendly “X days” label, use custom formatting:

0 "days"

That way the cell still holds a numeric value, but the display reads nicely.

Common Pitfalls to Watch For

• Text dates: If a date is stored as text, subtraction will return #VALUE!. Convert with DATEVALUE or Text‑to‑Columns.
• Time components: Dates with times (e.g., 2024‑04‑01 14:30) affect the result. Use INT to strip the time portion if you only need whole days.
• Leap year quirks: Excel handles leap years correctly, but double‑check any manual calculations that span February 29.

Quick Reference Cheat Sheet

• Days: =B2‑A2
• Business days: =NETWORKDAYS(A2,B2)
• Full months: =DATEDIF(A2,B2,"M")
• Full years: =DATEDIF(A2,B2,"Y")
• Custom “X years, Y months, Z days”: see DATEDIF combo above
• From a date to today: replace B2 with TODAY()

With these tools in hand, calculating any date span in Excel becomes a matter of copying the right formula and adjusting a few cell references. Give them a try in your next budget, project plan, or attendance log—your future self will thank you.