The TIME function saves you from having to remember the formula for converting decimal minutes to an Excel time. However, note that the TIME function will "roll over" back to zero when values exceed 24 hours.
For example, 25 hours of time is 1500 minutes. Note the differences:
=TIME(0,1500,0)=0.041667=1:00 AM same day
=1500/1440=1.041667=1:00 AM next day
Subtracting minutes from time
You may get an error if you try to subtract minutes from a time, when the result is negative, because Excel doesn't allow negative time values.
One way to avoid this problem is to use a formula like this:
Here MOD function takes care of the negative problem by using the MOD function to "flip" negative values to the required positive value.
Another way to avoid this problem is to start with a time that includes a date value. This lets you subtract very large numbers of minutes without any danger of getting a negative result. If you don't want to see the date displayed in the result, just apply a time-only number format.
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Minutes Value Time 1 1/24 60 0.04167 1:00 3 3/24 180 0.125 3:00 6 6/24 360 0.25 6:00 4 4/24 240 0.167 4:...
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.