Calculate Hours and Minutes Between Two Times

Do you want to know how to calculate how many hours and minutes have elapsed between two times? Below, I’ll show you how to do that with Excel formulas. The difference won’t be in just fractions of days or hours but real minutes and hours that make it easy to measure. To make this work, however, the first step is entering in the time correctly.

How to enter time in Excel

Excel has a built-in time function called TIME where you can enter the hour, minute, and second. For example, if you wanted to enter a time of 6:30 AM, you could enter a formula of TIME(6,30,0). Alternatively, you could type in 6:30 AM — the key is leaving the space between the time and the AM/PM indicator.

However, the one clear limitation here is that this won’t help you if you want to calculate the hours and minutes between times that span more than just one day. By using the TIME function, or entering just the hours and minutes, Excel is always going to assume you’re talking about the current day.

The best way to enter in time is to also factor in the date. Depending on your regional settings you might enter this differently, but this is how I’d enter a time of 8:00 PM on Nov. 20 on my computer:

2020-11-20 8:00 PM

I can also use a 24-hour clock and type in the following:

2020-11-20 20:00

Either way, Excel knows what time I’m talking about. If you always want to return the current time, you can use the NOW function.

For the start time, I’ll set it to the start of the year: 2020-01-01 0:00

Calculating the difference

Just using the minus operator, I can get the difference between these two dates as follows:

By default, Excel will return the number of days, including the fractional days as well. To convert days and calculate the hours instead, we will multiply the difference by 24, since that’s how many hours are in a day. That gives us the following:

That’s 7,796 hours between those two dates and times. It’s a nice round number but what if we changed it so that the end date was at 8:30 pm, or 20:30, this is what the updated calculation would look like:

Now I’ve got that residual 0.50 which indicates half an hour. But I want minutes, not fractions of an hour. The easiest way to do this is to create one calculation for hours, and another for minutes. Then, afterwards, you can concatenate them together. To get the total hours, I’ll adjust my formula to include the ROUNDDOWN function so that it does not include the 0.5. It looks something like this:

=ROUNDDOWN(datedifference*24,0)

Where datedifference is that raw calculation between the two dates and times. In my calculation, I’m still multiplying the time difference by 24 to get to hours, and then I round that to 0 spots, which is indicated by the 0 in the second argument. Now it will only show 7,796.00 for hours.

To calculate the number of minutes, I’ll need to multiply the datedifference by 24 and then again by 60, to convert the difference into minutes. This is what my calculations look like thus far:

My hours are nicely rounded but my minutes include the total minutes, which is not what I want. I only want the minutes that are left over after the hours are factored out. Here I can make use of the MOD function which will tell me the remainder after division. I’ll adjust the minute calculation to calculate the remainder after I’ve divided the total minutes by 60. This will determine what’s left over after pulling out full 60-minute hours, which is that residual 0.50 that I’m after. Here’s what this formula looks like:

=MOD(datedifference*24*60,60)

That gives me the following result for minutes:

Now I get a nice and round 30 minutes. There is the potential that I can also get partial minutes if I have seconds in my calculation. This could be the case if I’m using the NOW function. To correct for this, I can again use the ROUNDDOWN function as I did for hours.

However, let’s assume that you also want to track seconds. We can do that as well. I’ll break out another column for seconds. There, I’ll multiply the difference by another factor of 60, to get the following:

I added 25 seconds to my end date, which is why you’ll notice there’s a slight change in the difference column from this screenshot and the earlier one. Right now, total seconds tells me there are 28,067,425 seconds between these two times. If I want to get the raw number of seconds, then I’ll again use the MOD function and again use 60 as a divisor, since now I want to factor out the minutes:

I now have a clean breakdown between hours, minutes, and seconds between these two times. But if you want to calculate more than just hours between two times, you can also incorporate the number of days as well.

Breakdown of days, hours, minutes, and seconds

If I wanted to take a different approach and break the difference down by days, and then by hours, minutes, and seconds, I’ll first need to break out the days. Since that’s the default calculation for Excel, all I need to do is use the ROUNDDOWN function on the difference. The formula is as follows:

=ROUNDDOWN(difference,0)

And that gives me this:

If I wanted to get the hours that are remaining, what I can do is take the difference, use the MOD function, but this time I’m using a divisor of just 1, since I really only want the decimal place after the full number. Then I’ll multiply that by 24 hours, and again, use the ROUNDDOWN function:

=ROUNDDOWN(MOD(difference,1)*24,0)

Now my hours total looks like this:

I’ve got a nice round 20 hours, which makes sense since 8:00 PM is 20:00 on a 24-hour clock. To calculate the difference in minutes, I can revert back to the earlier calculation where I used the MOD function to determine what’s left over after multiplying the difference by 24 and 60, and then dividing it by 60 minutes:

The seconds calculation will work the same way as well. The only difference in the way to break out hours and days was to adjust the hours calculation to ensure it isn’t taking in the full hour difference, only the residual amount that pertained to the current day.

Now that you’ve got all the chunks broken down between days, hours, minutes, and seconds, you could concatenate that into one large formula, Something like this might work:

=CONCATENATE(daydifference,” Days “,hourdifference,” Hours “,minutedifference,” Minutes “,seconddifference, ” Seconds”)

That produces the following:

For this not to be a messy result, you’ll want to ensure you’re using the ROUNDDOWN function in each of those calculations so that you aren’t keeping any trailing numbers.

If you liked this post on how to calculate hours between two times, 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.