Calculate Elapsed Days, Hours and Minutes in Excel

I had an email from Steve a little while ago asking how he could display the number of elapsed days, hours and minutes in one cell as d h:mm, like this in C5 below:

Notes:

1. my dates are dd/mm/yyyy.

2. In this tutorial I am assuming you're familiar with how Excel uses serial numbers for dates and time. If not click here to learn about dates and time in Excel first.

Ok, now you know all about serial numbers let's look at my formula in cell C5:

=INT(end-start)&" "&TEXT(end-start,"h:mm")

A5 = start

B5 = end

The INT function simply rounds the number down to the nearest integer, or whole number.

In English it reads:

Calculate the whole number of days between the end and start dates (this will give the days figure), & add a space & then calculate the end minus start and display the hours and minutes portion as text formatted as h:mm.

We can see it evaluate in stages like so:

Step 1: = 2&" "&14:00

Step 2: 2 14:00

The limitation of this result is that it is text as opposed to a number, which means it can’t be used in another formula that SUM’s or AVERAGE’s etc., as these functions require numbers.

I have done this because the number of elapsed days could be > 31 and Excel cannot display cumulative days > 31 as well as time in one cell.

If you’re confused about this last statement, keep reading and all will become clearer with the next example….hopefully!

Elapsed Days and Time as a Number

If the number of days between your start and end dates is always <32 days, then you can use a much simpler formula and apply a custom number format to the cell like this in cell D5:

Formulas don’t get much easier than that!

To display the elapsed time as d h:mm I simply used this custom number format:

The Limitation

You can see in the example above the last calculation in cell D8 gives a different result to C8, and this is because the number of days is >31.

In D8 we get 23 days when it should be 54 days. This is because 54 days from the 1^st Jan 1900 (the day Excel dates start), takes us up to the 23^rd Feb 1900 and Excel is simply displaying the day portion (23) of the underlying serial number Excel uses for dates.

This is what I mean by 'Excel cannot show the cumulative number of days using a custom number format for dates/time in one cell'.

So, if your elapsed time is likely to exceed 31 days you need to use the first option which results in text. If you also need the elapsed time calculated as a number for use in other formulas then you will need to use helper columns to house that calculation.

In case you missed it, here's a link to my tutorial on working with Time in Excel which explains how serial numbers are used to determine dates.

Thanks

Thanks to Steve for asking this great question. It highlights some of the constraints when working with time in Excel.