Having to calculate date and time difference in Excel is a common task. Unfortunately, depending on your requirements it is also not always a simple one.
Take this example where column A contains a start date and time, and column B an end date and time. We wish to calculate the elapsed time in days, hours and minutes e.g. 11 days 4 hours 9 minutes.
There are multiple ways of calculating date and time difference in Excel. In this scenario we will need to get a little clever.
As you may well know, date and time values are stored as numbers in Excel. For example, the 05/01/2017 10:10 is stored as 42740.42.
Therefore, if I write the formula as =B2-A2, then the result is returned as 2.993056.
To return a result that makes sense to us, we will tackle the date and time parts of the cell separately.
Calculate the Number of Days Difference
To work with just the date part of the cell, we will use the INT function. This function rounds a value down to the nearest integer.
So, if we write the function as below. This will return only the integer part of the date difference, and this is the number of days.
=INT(B2-A2)
Calculate the Elapsed Hours and Minutes
We now need to work on the number of hours and minutes, which is the decimal part.
To return only the decimal part of the B2-A2 formula, we will use the MOD function. This function returns the remainder after a number is divided by a divisor.
We will use it to divide the B2-A2 formula by 1 so that it returns to remainder as the decimal part.
We will then use the HOUR and MINUTE functions to return the hours and minutes from this decimal value.
So the formula below returns the number of hours elapsed.
=HOUR(MOD(B2-A2,1))
And this returns the number of minutes elapsed.
=MINUTE(MOD(B2-A2,1))
Excel Formula for Elapsed Time in Days, Hours and Minutes
Finally, we need to put this altogether as one Excel formula. We can use the ampersand (&) to concatenate the different parts of the formula.
You can construct the result to look however you want. For example the formula below would return the result as 2 days 23 hours and 50 minutes.
=INT(B2-A2)&" Days "&HOUR(MOD(B2-A2,1))&" Hours "&MINUTE(MOD(B2-A2,1))& " Minutes "
And this formula would display the result as 2 days 23:50.
=INT(B2-A2)&" Days "&HOUR(MOD(B2-A2,1))&":"&MINUTE(MOD(B2-A2,1))
Leave a Reply