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.
To convert minutes in decimal format to a proper Excel time, divide by 1440. In the example shown, the formula in C6 is: = B6 / 1440 Because B6 contains 60 (representing 360 minutes) the result is 60/1440 = 0.04167, since 60 minutes = 1 hour = 1/24...
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.