In some situation, you may record the time in decimal hours (ex. 41.25) and in some cases, it needs to be added with the standard time format HH:MM:SS. How do you do this? is explained in the below sections.
How to add decimal hours to time in Excel?
There are 2 methods in Excel which can be used to add decimal hours to time.
- One method is to add decimal hours to time is to just simply add the hours to the time and then divide by 24.
- The other method is to use the built-in TIME function in Excel which saves you from remembering the formula for conversion of decimal hours to time.
Using the divide hours by 24 method
The Generic formula for this method is as given below.
How does the formula work?
In the above Generic formula the generic parameter ‘time’ denotes the time to which the decimal hour is to be added and the ‘hours’ is the decimal hour to be added. This parameter is simply divided by 24 and added as shown in the formula. This is done because in Excel time is a fractional value of 24 and therefore one hour is 1/24 where ‘1’ denotes the hour.
- Let us consider a table containing a list of timings and hours which is to be added to it.
Figure 1. Table showing the time and hours to added
- Here the generic formula used is ‘=A2+B2/24 where the time parameter is the cell A2 and the hours to add cell is B2.
Figure 2. The formula applied to add decimal hours to a time
- Similarly using the same formula all the remaining results are obtained.
Figure 3. The result obtained on applying the formula
Using TIME function method
The TIME function is a built-in function of Excel that can also be used to add decimal hours to time. However, the disadvantage of using this function is that, if the value of the decimal hour exceeds 24 hours then it rolls back to ‘0’.
How does the formula work?
The generic parameter ‘A1’ denotes the time. The TIME function returns the equivalent value of 1 hour whereas the divide by 24 method returns the full value(25/24).
- Let us consider the same example as shown for the divide by 24 methods but this time we will use the Time function. The hour parameter value is always added with 24 to avoid roll off as shown in the figure below.
Figure 4. Using TIME function for the same example
- The result obtained is similar to that obtained previously.
Figure 5. The result obtained using the TIME function
Subtracting hours from the time
We have learned how to add hours to time, now let us see how to subtract hours from time. While doing this you may get an error because Excel does not allow negative time values.
How to avoid the error?
- To avoid the error we can make use of the MOD function as shown in the generic formula given below.
=MOD(time - (hours /24),1)
Here the MOD function has 2 parameters ‘number’ and ‘divisor or mode’. Here the negative problem is solved which flips the negative values to an equivalent positive value depending on the value of the mode.
- Another method is to start with a time that includes a date value and this allows you to subtract a larger number of hours without any problem of negative time values. Note that if you do not want the date to be displayed then just apply a time_only format for the result.