Calculating time elapsed in Excel helps you measure durations between two moments, such as hours worked, project length, or event intervals. This guide shows practical formulas and formatting tips to make your time differences accurate and easy to interpret.
Whether you are tracking minutes, days, or months, Excel provides multiple approaches that adapt to different business and personal use cases.
| Goal | Formula | Result Format | Notes |
|---|---|---|---|
| Simple hours difference | =B2-A2 | h:mm | Basic duration in hours and minutes |
| Total decimal hours | =HOUR(B2-A2)+(MINUTE(B2-A2)/60) | Number | Useful for payroll and billing |
| Elapsed days ignoring time | =INT(B2-A2) | Number | Returns whole days as an integer |
| Complex duration with days and hours | =DAYS(B2,A2)&" days "&TEXT(B2-A2,"h"" hrs ""m"" mins """) | Text | Human-readable summary spanning multiple units |
Using Simple Subtraction for Time Elapsed
Subtraction is the fastest method to calculate time elapsed between a start and end timestamp. Enter =EndTime-CellStartTime in a new cell, then apply a time-based format such as h:mm or [h]:mm to display hours that may exceed 24.
Excel stores dates and times as numbers, so subtracting two datetime cells yields a fractional day value. Multiplying that value by 24 gives total hours, while multiplying by 1440 returns total minutes.
When your duration crosses midnight, ensure the result cell uses a bracketed hour format like [h]:mm to avoid negative or wrapped times that hide true elapsed time.
Formatting Cells for Accurate Display
Correct cell formatting keeps long durations readable and prevents confusing results like negative times or values that roll over to zero.
- Use [h]:mm:ss for durations longer than 24 hours
- Apply mm:ss to compare lap times or short intervals
- Use m/d/yyyy h:mm AM/PM for clear datetime labels
Custom formats let you control how hours, minutes, and days appear without altering the underlying numeric values.
Total Hours and Minutes for Payroll
For payroll and billing, you often need total hours as a decimal number rather than a time value.
Decimal Hours Formula
=HOUR(B2-A2)+(MINUTE(B2-A2)/60)+SECOND(B2-A2)/3600
Rounded Hours with MROUND
=MROUND(B2-A2,"0:15") rounds to the nearest 15 minutes, helping standardize time entries for invoicing.
Working with Days, Weeks, and Months
When you need whole days instead of hours and minutes, the INT function extracts the day portion, while DAYS gives direct day counts between two dates.
Whole Days Only
=INT(EndDate-StartDate)
Counting Specific Units
=DAYS(EndDate,StartDate) returns total days, and you can divide by 7 or 30 for quick week or month approximations.
Key Takeaways for Time Elapsed in Excel
- Use simple subtraction and proper formatting to avoid negative or zero results
- Convert fractional days to hours or minutes for billing and payroll
- Use DAYS, DATEDIF, and CEILING for specialized durations
- Bracket hour formats like [h]:mm keep long durations readable
FAQ
Reader questions
How do I calculate elapsed hours when the shift crosses midnight?
Use =IF(EndTime<StartTime, EndTime+1, EndTime)-StartTime and format the result as [h]:mm to show correct hours even after midnight.
Can Excel calculate elapsed time in months and years, not just days?
Yes, use DATEDIF(StartDate,EndDate,"m") for total months or DATEDIF(StartDate,EndDate,"y") for full years, then add remaining months with DATEDIF(StartDate,EndDate,"ym").
What is the best format to display durations over 100 hours?
Apply [h]:mm:ss custom format so hours accumulate correctly instead of resetting at 24. =CEILING((EndTime-StartTime)*1440,15)/1440 rounds minutes up and keeps the result as a time value that you can format as [h]:mm.