Calculating time between dates in Excel helps you measure project duration, track aging, and analyze timelines without manual math. With the right functions and format settings, you can quickly get exact differences in days, months, or years.
This guide walks through core techniques, practical examples, and common pitfalls so you can handle duration calculations confidently in real workbooks.
| Function | Purpose | Example Result | Best For |
|---|---|---|---|
| DATEDIF | Difference in days, months, or years | 45 days | Precise interval parts |
| DAYS | Simple day-level difference | 45 | Quick day counts |
| DAYS360 | 360-day year basis | 44 | Financial and accounting standards |
| TEXT subtraction | Formatted duration display | 45 days | Readable reports and labels |
Using DATEDIF for precise intervals
DATEDIF is a hidden function that returns the difference between two dates in years, months, or days based on the unit you choose. It supports three unit options: "Y" for complete years, "M" for complete months, and "D" for total days between dates.
Use start_date and end_date in their actual Excel serial format or reference cells containing valid dates. The function ignores time portions if they are present, so 1-Jan 09:00 and 2-Jan 15:00 still count as one full day when using the day unit.
Example: =DATEDIF(A2,B2,"D") returns the total days, while =DATEDIF(A2,B2,"M") returns complete months only, which is useful for tenure or subscription spans.
Basic day difference with DAYS
The DAYS function delivers a straightforward count of days between two dates by subtracting the start date from the end date internally. It requires only end_date and start_date arguments, making it quick to type and audit.
Unlike DATEDIF, DAYS includes partial days in the result based on the underlying serial values, so it can show fractions when times are involved. If you need whole days only, pair DAYS with INT or round according to your business rule.
Example: =DAYS(B2,A2) is simpler to read than =B2-A2 and communicates intent clearly when documenting duration logic.
Handling financial years with DAYS360
DAYS360 calculates duration based on a 360-day year with 30-day months, aligning with certain accounting standards and older financial contracts. The method uses either US or European calculation rules depending on the mode you select.
Set the method argument to FALSE for the US NASD approach or TRUE for the European method, which adjusts dates at the end of months. This predictable behavior avoids month-end quirks that can appear with actual day counts.
Example: =DAYS360(start,end,TRUE) provides consistent month slicing for interest accruals, lease term calculations, or any scenario requiring fixed 30-day month assumptions.
Displaying duration in readable text
You can combine basic subtraction with the TEXT function to present intervals in natural language such as "45 days" or "3 months, 12 days". This approach is helpful for reports where clarity trumps further calculation.
Wrap the subtraction inside TEXT and use a duration-based format string like "d" for days or combine larger units with concatenation. Keep in mind that TEXT returns a string, so you cannot use the result directly in further numeric operations without converting it back.
Example: =TEXT(B2-A2,"d")&" days" produces a clean label that readers instantly understand without parsing raw numbers.
Key practices for date duration calculations
- Validate date inputs with ISDATE or cell format checks to avoid misleading results.
- Use DATEDIF when you need distinct parts like years, months, and days in one calculation.
- Choose DAYS for simple day-level differences and DAYS360 for standardized financial intervals.
- Apply TEXT sparingly for display only, and keep numeric results separate for downstream formulas.
- Plan for time zones and daylight saving shifts by storing UTC timestamps or normalizing inputs.
FAQ
Reader questions
How do I count workdays only, excluding weekends and holidays?
Use the WORKDAY.INTL function with a start date, number of workdays, and an optional holiday range to skip weekends and custom nonworking days.
What if my dates appear as text and calculations return errors?
Convert text dates to real serial numbers with DATEVALUE, or use VALUE after cleaning the input, then reapply your duration formula.
Can I calculate elapsed months and days together, like 2 months, 15 days?
Yes, combine DATEDIF with concatenation: use DATEDIF for months and the remainder days, then join them with ampersand text operators for a single readable result.
How do I include time portions in the duration instead of whole days only?
Subtract the datetime values directly and format the result as [h]:mm or apply custom elapsed time formats to show hours, minutes, and seconds accurately.