Elapsed Time in Excel Formula
The definitive tutorial for project managers, payroll specialists, and office administrators.
The Excel Time Paradox
Calculating the time between two events seems like it should be simple addition or subtraction. However, Excel treats time as a fraction of a day (where 1 = 24 hours). This hidden logic is why many office workers see nonsensical numbers like “0.25” when they expect to see “6 hours.”
In this pillar guide, we will break down the exact formulas and custom formatting required to calculate elapsed time (durations) accurately, whether you are tracking employee shifts, project timelines, or server uptime.
1. The Core Formula: End Time Minus Start Time
The golden rule of time calculation in Excel is: End Time – Start Time. If your start time is in cell A2 and your end time is in B2, your formula is simply:
The Formatting Catch
By default, Excel might display the result as a specific time of day (e.g., 6:00 AM) rather than a duration (6 hours). To fix this, you must change the Number Format:
- Right-click the cell and select Format Cells.
- Go to the Number tab and select Time.
- Choose the format 13:30 or 37:30:55 for durations.
2. Handling Over-Midnight Shifts
If an employee starts at 10:00 PM and finishes at 6:00 AM, the simple =B2-A2 formula will return a “#####” error or a negative number because Excel thinks the end time is “before” the start time on the same day.
The MOD Formula Solution
To handle shifts that cross the midnight barrier, use the MOD function. This tells Excel to ignore the negative result and treat the day as a 24-hour cycle:
This is the most reliable formula for office timesheets where overnight work is common.
3. Calculating Totals Beyond 24 Hours
If you sum a column of durations that add up to 30 hours, Excel will often show “6:00.” This is because it “wraps” around 24 hours (30 – 24 = 6). To display the actual cumulative total, you must use Custom Formatting:
- Select your Total cell.
- Press Ctrl + 1 (Format Cells).
- Select Custom.
- In the “Type” box, enter: [h]:mm:ss
The square brackets [h] tell Excel to “stop resetting at 24” and show the total hours instead.
4. Essential Formula Reference Table
| Goal | Excel Formula |
|---|---|
| Simple Duration | =B2-A2 |
| Across Midnight | =MOD(B2-A2, 1) |
| Difference in Minutes | =(B2-A2)*1440 |
| Difference in Seconds | =(B2-A2)*86400 |
| Convert Time to Decimal | =(B2-A2)*24 |
5. Frequently Asked Questions (Office FAQ)
Why is my Excel time result showing 0.00?
Your cell is likely formatted as a “Number” or “Currency.” Change the format to “Time” or “Custom” [h]:mm to see the actual hours.
How do I subtract 30 minutes for a lunch break?
Use the formula =B2-A2-(30/1440). Since Excel treats a day as 1, you must divide minutes by 1440 (the minutes in a day).
Can Excel calculate the number of days between dates?
Yes. Simply subtract the date cells: =End_Date – Start_Date and format as a Number.
Why does Excel show “####” in my time cell?
This usually happens when the result is a negative time (unsupported by default) or the cell is too narrow to show the data.
How do I calculate age from a birthdate in Excel?
Use =DATEDIF(Birth_Date, TODAY(), “y”) to get the age in years.
How to convert 24-hour time to AM/PM?
Simply change the cell format to h:mm AM/PM. The underlying data stays the same.
6. Conclusion: Precision Matters
Excel is a powerful tool, but its handling of time is a common point of frustration for office workers. By mastering the MOD function and the [h] formatting tag, you can eliminate 90% of the errors in your reports. However, for complex payroll or duration checks, using a dedicated web-based calculator provides an essential “second opinion” to ensure your data is 100% accurate.