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.
Times in Excel are factional values of 24 hours. One hour of time is 1/24, and 1 minute of time is 1/(24*60) = 1/1440. As a result, if you have a decimal value for 15 minutes, and a time in A1, you can add 15 minutes of time to the value in A1 like...
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:...
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 Value Time 1 1/24 0.04167 1:00 3 3/24 0.125 3:00 6 6/24 0.25 6:00 4 4/24 0.167 4:00 8 8/24 0.333 8:00 12...
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.