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.
In other words, as we approach midnight, the value of time approaches 1.
To do this, we simply divide the minutes by 1440.
The results in column E are formatted with thecustom number format"h:mm AM/PM".
With the TIME function
you might also add time values with theTIME function.
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.
For example, 25 hours of time is 1500 minutes.
This requires no adjustment in the formula explained above.
This happens when the number of minutes being subtracted is larger than the original time value.
Here, MOD works like a clock, wrapping the time around when it goes past 24 hours.
Note: This is a good example of “modular arithmetic”, sometimes called “clock arithmetic”.
This lets you subtract very large numbers of minutes without any danger of getting a negative result.
For example, the numeric value in cell B5 is 45474.3333.
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.
