Calculating Elapsed Time in Excel

Introduction: Why Calculating Time is Tricky

At first glance, calculating elapsed time in Excel seems simple: just subtract the start time from the end time. But if you’ve ever tried this, you’ve probably ended up with a strange decimal number or even a string of hashtags.

This happens because Excel treats time as a fraction of a day. For example, 6:00 AM is represented as 0.25 and 12:00 PM is 0.5.

To get a result you can actually use, you need to understand a few key formulas and formatting tricks. This guide will walk you through the correct way to calculate time differences, whether it’s for a simple task, a long work shift, or a multi-day project.

The Basic Formula: Subtracting Two Times

The core of all elapsed time calculations is a simple subtraction.

The Core Concept: To find the duration between a start time and an end time, use the formula:

=End Time - Start Time

Formatting is Key: When you enter this formula, the result will likely appear as a decimal. You must format the cell to display it as a time value. Right-click the cell, select “Format Cells…”, and then choose a custom format.

For hours and minutes: Use h:mm
For hours, minutes, and seconds: Use h:mm:ss

Pro Tip: If your elapsed time might be longer than 24 hours (for example, a very long flight or a work shift over several days), you must put square brackets around the h: [h]:mm. This tells Excel to display the total number of hours, not just the remainder after a full day.

Example 1: A Short Task

	A	B	C
1	Start Time	End Time	Elapsed Time
2	9:00 AM	11:30 AM	`2:30`

In cell C2, the formula is =B2-A2. Make sure C2 is formatted as h:mm.

Calculating Time Spans that Cross Midnight

A common issue arises when a work shift or event starts one day and ends the next. A simple subtraction will give you a negative number, which Excel displays as #########.

The Solution: To correct for the overnight shift, you need to add 1 to the result. The formula becomes:

=(End Time - Start Time) + 1

Example 2: An Overnight Shift

	A	B	C
1	Start Time	End Time	Elapsed Time
2	10:00 PM	6:00 AM	`8:00`

In cell C2, the formula is =(B2-A2)+1. Format C2 as h:mm. This corrects the negative value and gives you the correct duration of 8 hours.

Calculating Elapsed Time with Dates and Times

For projects or tasks that span multiple days, you’ll need both a start date and time and an end date and time. Fortunately, this is where Excel’s intelligence comes in handy.

The Simple Solution: If you have a full date and time in each cell, the basic formula still works.

=End Date/Time - Start Date/Time

Formatting the Result: The result will be a decimal number representing the total days and a fraction of a day. You can format this cell to show the duration in a more readable format.

To show total hours: [h]:mm
To show days and hours: d "days," h:mm
To show days, hours, and minutes: dd "days" hh:mm

Example 3: A Multi-Day Project

	A	B	C
1	Start Date/Time	End Date/Time	Elapsed Time
2	9/20/25 8:00 AM	9/22/25 10:00 AM	`2 days, 2:00`

In cell C2, the formula is =B2-A2. Format C2 as d "days," h:mm.

Common Problems and Troubleshooting

Getting a decimal like 0.25? Your result cell is not formatted for time. Right-click, go to “Format Cells,” and choose a time format like h:mm or [h]:mm.
Seeing #########? Your start time is later than your end time. This usually happens with overnight shifts. Use the formula =(End Time - Start Time) + 1 to fix it.
Getting a result of 02:00 for a 26-hour period? You need to put brackets around the h in your custom format: [h]:mm. This will correctly display 26:00.

Takeaway

Mastering elapsed time calculations in Excel comes down to two simple rules: a basic subtraction formula and proper cell formatting. For most tasks, =End Time - Start Time will get the job done. For overnight shifts, you just need to remember the +1 trick.

Conclusion: Mastering Time Calculations

Calculating elapsed time in Excel is a common task that can be made simple with the right approach. By understanding how Excel handles time, knowing the basic formula, and paying close attention to cell formatting, you can easily and accurately track durations for any purpose—from billing for a client project to calculating employee work hours. With this guide, you now have the tools to handle time calculations with confidence, saving you frustration and ensuring your data is always precise.