How to Show Elapsed Time in Excel

Do you need to calculate the time that that has elapsed between two date values in Excel? In this post, I’ll show you how you can show the difference in hours, minutes, and seconds. This can be useful if you need to determine hours on a work shift or just to see how much time is remaining until a deadline.

The following table is what an employee’s shift schedule might look like over the course of a week:

You have the time they started work, left work, and the duration of their break. To calculate the time difference and net hours worked, this can be accomplished by the following formula:

Time Work : Time Out – Time In – Break

It’s just a simple subtraction formula. However, the tricky part is that by default, Excel will calculate this difference in days and so the result will be shown as a fraction of a day (since it is less than 24 hours):

There are a couple of ways to fix this. The first way is to multiply the results by a factor of 24 so that the calculation gets converted into hours:

The caveat here is that now instead of fractions of a day, you now have fractions of an hour. If you prefer to not do any conversions and instead just want to display the value as elapsed time as hours and minutes, that can be done by formatting the cells, which is the alternative method.

To do this, select the cells in the Total Time column and select CTRL+1 to Format Cells. From there, go to the Custom category and enter [h]:mm as follows:

By doing this, the result will be similar to when you multiplied the values by 24:

An important difference you’ll notice is that the Total Time column shows in terms of hours and minutes, whereas the Hours column still shows fractions of an hour. For instance: 9 hours and 30 minutes shows up as 9:30 in Total Time but under the Hours column it is 9.50. One column is showing the actual minutes while the other is showing it in terms of fractions of an hour.

If you wanted to only show the number of minutes elapsed, the time format would simply be [m]. Then, your time would show in terms of minutes.

And to show the time in seconds, use [s]:

You could, of course, do all of these conversions by multiplying the hours field by 60 to convert it into minutes and then by 60 again to convert into seconds. By just changing the number format, you aren’t doing any changes to the original calculation. Either option can get the desired end results. However, if you want to specifically show hours and minutes and seconds, and not fractions of an hour, you’ll want to use either [h]:mm or perhaps [h]:mm:ss if you have your time broken down to the second.

If you liked this post on How to Show Elapsed Time in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.