Explanation
In this example, the goal is to add hours in decimal format (i.e.
1, 2, 3, etc.)
to an existing Excel time.
The complication is that Excel stores time as fractional values.
But it isn’t the way most people think about time.
As a result, time is a fractional value of 1 and 1 hour = 1/24 = 0.041666667.
In other words, as we approach midnight, the value of time approaches 1.
To do this, we simply divide the hours by 24.
The results in column E are formatted with thecustom number format"h:mm AM/PM".
However, note that the TIME function only supports time spans up to 24 hours.
Every 24 hours, the time will reset to zero like a clock.
Another way to avoid this problem is to start with a time that includes a date.
This lets you subtract very large numbers of hours without any danger of getting a negative result.
For a more detailed discussion of this topic seeCalculate hours between two times.
This lets you subtract a large number of hours without the danger of getting a negative result.
For example, the numeric value in cell B5 is 45474.3333.
Notice the results in rows 14-16 are valid and work fine.
The TIME function is useful when you want to assemble a proper time inside another formula.
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.
Related videos
How to use time formatting in Excel
