Explanation
In this example, the goal is to calculate the number of hours between two times.
In part, this is because Excel stores time as fractional values.
For example, 0.25 is 6:00 AM or 6:00 hours, depending on formatting.
But it isn’t the way most people think about time.
The other reason time is complicated is that it resets to zero again at midnight.
(2) Do time durations exceed 24 hours?
(3) Are the times part of a date?
The article below describes three formulas for calculating the number of hours between two times.
It also explains how to format time and calculate the hours between times as a decimal value.
Table of Contents
Formula options
Below are the three formulas explained in this article.
The first formula works fine if the times occur on the same day and do not cross midnight.
It also works well fordates that contain time, which isexplained here.
The third formula is an elegant alternative to the second formula.
To start, let’s review how Excel handles time.
Time is a fractional value of 1, and 1 hour = 1/24 = 0.041666667.
This formula works well for times that occur on the same day.
However, if times cross midnight, it will fail.
Formula 2: When times cross midnight
Excel handles time as a 24-hour clock.
As a day progresses, the time value increases, approaching 1 toward midnight.
This makes sense as a clock, but it makes calculating time more difficult when times cross midnight.
TheIF functionchecks the times and applies the correct formula.
This works because 24 hours is 1 day, and the point at which time resets to zero.
When we subtract the start time from 1, we get the time until midnight.
When we add the end time, we add the time from midnight until the end time.
The two times together are the total elapsed time.
Otherwise, the times cross midnight, and the second formula is used.
The MOD function returns the remainder after division.
The result has the same sign as the divisor.
Inside MOD, we supply thenumberby subtracting the start time from the end time.
For thedivisor, we provide the number 1.
When you divide any number by 1, the result is the number itself.
This is because division by 1 does not change the value of the number.
However, when MOD calculates a remainder, it handles positive numbers differently from negative numbers.
We can use this difference to our advantage with time in Excel.
For a good introduction to modular arithmetic, see thislink on Khan Academy.
Note: The formulas above willnothandle durations greater than 24 hours.
If this is a requirement, see the “Dates with times” section below.
Below are two common number formats for time.
Calculating decimal hours between times
The formulas above all return native Excel time as a result.
For such calculations, you will want to convert the result todecimal hours.
The number format in the worksheet above is “0:00” to display a number with two decimal places.
you’re able to use “0.0” to display a single decimal place.
More than one condition can be tested by nesting IF functions.
The IF…
MOD Function
The Excel MOD function returns the remainder of two numbers after division.
For example, MOD(10,3) = 1.
The result of MOD carries the same sign as the divisor.
