Calculate elapsed work time

An excel formula to calculate elapsed work time

Related Functions

Sponsored Link

 Calculate elapsed work time

If you need to calculate elapsed time you can use a simple formula that simply subtracts the start time from the end time. However, when times cross a day boundary, things can get tricky. Read below to see several ways you can calculate elapsed time, depending on the situation.

The basics

In Excel, one day (24 hours) is represented by the number 1. So 1 hour is 0.041666667 (i.e. 1/24), 8 hours is 0.333, 12 hours is 0.50 and so on. In short, you can think of hours as fractional pieces of a day.

When the start time and end times are both in the same day, then the start time is, by definition, less than the end time and you can use simple subtraction to figure out elapsed time. For example, with start time of 9:00 AM and an end time of 5:00 PM, you can simply use this formula:

end - start = elapsed time 0.375 - 0.708 = .333 // 8 hours

Formatting elapsed time

To format elapsed hours it can be useful to use a custom format like h:mm or [h]:mm

The special square bracket syntax [h] tells Excel to allow hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

The problem

Calculating elapsed time is more tricky if the times cross a day boundary. For example, if the start time is 10:00 PM one day, and the end time is 5:00 AM the next day, the end time is actually less than the start time and the formula above will return a negative value which will cause Excel to display a string of hash characters (i.e. ########).

To correct this problem, you can use this formula for times that cross a day boundary:


By subtracting the start time from 1, you get the amount of time in the first day, which you can simply add to the amount of time in the 2nd day, which is the same as the end time.

This formula won't work for times in the same day, so we can generalize and combine both formulas inside an IF statement like so:

=IF(end>start, end-start, 1-start+end)

Now when both times are in the same day, end is greater than start time, so the simple formula is used. But when the times across a day boundary the second formula is used.

This can be further simplified to this elegant formula:


Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value.

A thorough discussion of modulo is beyond the scope of this article, but here's a good link on Khan Academy.

So, the formulas above will handle either case (both times in the same day, or start in one day and end in the next). However, note that they only work for times that span just one day. If times span more than one day, you'll need a different approach. One approach is to use both date and time, as explained below.

Simplify the problem - use date and time

If you don't like the complexity of the above solutions, or if you need to calculate elapsed time that spans more than one day, an easy fix is to simply add a date value to both the start and end times. For example, you can enter September 1, 2016 at 9:00 AM as follows, with a single space between time and date:

9/1/2016 10:00 AM

Because this stores both the date and time, you can always subtract the start from the end and get a correct result.

For example, to calculate the elapsed hours between September 1, 2016 at 9:00 AM and September 3 at 10:00 AM, enter both values as dates plus times, then subtract the start from the end and use [h]:mm to format the result.

Note that when you use a date and time, you can format the values any way you like. You can apply a format that shows the date with a time, or simply format to show time only.

The formula in D8 the screen shot example at the top of the page uses this approach. The formula is simply:


The result is 2.042, which, when formatted using [h]:mm, is 49:00 hours.

Sponsored Link

0 votes. 0 / 5