The TIME function saves you from having to remember the formula for converting decimal hours to an Excel time. However, note that the TIME function will "roll over" back to zero when values exceed 24 hours.
For example, note how the two approaches return different results for 25 hours:
=TIME(25,0,0)=0.041667=1:00 AM same day (1 hr)=25/24=1.041667=1:00 AM next day (25 hrs)
The TIME function returns the equivalent of 1 hour, while 25/24 returns the full value.
Subtracting hours from time
You may get an error if you try to subtract hours 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 hours 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 add a given number of minutes to a time, you can add minutes divided by 1440, or use the TIME function. In the example shown, the formula in D5 is: = B5 + ( C5 / 1440 ) Note: make sure results are formatted as time. How this formula works Times...
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...
To convert hours in decimal format to a value Excel recognizes as time, divide by 24. In the example shown the formula in C5 is: = B5 / 24 which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which...
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.