Calculating duration in Excel helps you measure time spans for projects, shifts, or events with precision. This guide walks you through reliable methods using formulas, custom formats, and data tools.
Use structured examples and built in functions to avoid common mistakes and keep your duration calculations accurate across different time units.
| Formula | Use Case | Result | Notes |
|---|---|---|---|
| =End-Start | Simple date time difference | Numeric days or decimals | Apply duration number format |
| =DATEDIF(start,end,"d") | Whole days between dates | Integer days | Ignores partial days |
| =DATEDIF(start,end,"md") | Days ignoring month and year | Days under 31 | Use with whole period dates |
| =HOUR((End-Start)*24) | Total hours as a number | Decimal hours | Multiply elapsed days by 24 |
| =TEXT(End-Start,"h:mm") | spanning daysHours and minutes | Duration clock display | Works for results under 31 days |
Using Duration Number Formats
Excel stores dates as numbers and times as fractions of a day. To see elapsed time correctly, apply a duration number format such as [h]:mm:ss.
Standard time formats can roll over at 24 hours, but square bracket h units keep total hours visible even for spans longer than a day.
Right click the cell, choose Format Cells, pick Custom, and enter [h]:mm:ss to display duration cleanly without shifting past day boundaries.
Basic Subtraction for Simple Durations
Subtract the start date time from the end date time to get raw elapsed days. For example, =B2-A2 returns the difference as a date serial number.
Format the result with a duration format to read hours and minutes instead of a distant future date or an incorrect negative value.
When start and end are on the same day, the result is a fraction of 24 hours, which you can display as decimal hours by multiplying by 24.
Handling Negative and Cross Day Durations
If the end time is earlier than the start time, Excel may display ##### or a negative date depending on format settings.
Use =IF(B2>=A2,B2-A2,1+B2-A2) to wrap night shifts that cross midnight and ensure positive duration results.
For decimal hours across midnight, try =(MOD(B2-A2,1))*24 and format the cell as Number to keep values consistent.
Calculating Duration with DATEDIF and Unit Control
The DATEDIF function lets you request years, months, or days between two dates, which is useful for partial duration breakdowns.
Use =DATEDIF(start,end,"d") for whole days, =DATEDIF(start,end,"m") for complete months, and =DATEDIF(start,end,"y") for full years.
Combine DATEDIF units in one formula to produce messages like 2 years 3 months 5 days for clearer reporting.
Best Practices for Duration in Excel
- Use [h]:mm:ss custom formats for durations longer than 24 hours
- Validate start and end inputs to avoid negative time surprises
- Wrap cross day subtractions with MOD or IF for consistent positive values
- Leverage DATEDIF when you need clean year month and day components
- Convert to decimal hours or minutes for billing and export needs
FAQ
Reader questions
How do I show hours over 24 correctly in a cell?
Apply the custom format [h]:mm:ss so total hours accumulate without rolling over at 24.
What if my end time is earlier and I get negative results?
Wrap the subtraction with an IF such as =IF(end>=start,end-start,1+end-start) to force positive duration when shifts cross midnight.
Can I return duration as text like 3h 15m instead of a number?
Yes, =TEXT(end-start,"h""h ""m""m "")" produces a readable label that you can concatenate into reports or labels.
How do I calculate duration in minutes for billing purposes?
Use =(end-start)*1440 and format as General to get total minutes, or =HOUR(d)*60+MINUTE(d)+SECOND(d)/60 for mixed hour and minute precision.