A number of options are available for adding time in Excel. We may use built-in functions “TIME” or “SUM”, depending on how we have recorded the time in the worksheet. Excel allows us to capture “Hour”, “Minute” and “Second” in separate cells as well as in one too. You can use suffix “AM” and “PM” or use military time (enter 14:00 for 02:00PM).

If we record the “hour”, “Minute” and “Second” in separate cells, we have to use the function “TIME” for calculation. Syntax for the function “TIME” is

TIME (hour, minute, second)

All the parameters in above function are mandatory. If we update “0” for all the parameters, excel will show “12:00AM”. In case we update an “hour” value with more than 23 it will be divided by 24 and only remainder will be considered as “hour”. Similarly, any value more than 59 for “minute” and “second” will be divided by 60 to convert the same in “hour” & “minute” or “hour”, “minute” & “second” as the case may be. To add the time maintained as hour, minute & second in column “A”, “B” & “C” respectively, we can use any of the below formula:

=TIME(A2,B2,C2)+TIME(A3,B3,C3)
=TIME(SUM(A2:A3),SUM(B2:B3),SUM(C2:C3))
=TIME(A2+A3,B2+B3,C2+C3)

For recording the time in single cell we have to separate “hour”, “minute” and “second” with “:”. By this Excel will automatically convert the cell formatting as “TIME”.

If we are not using military time and we don’t update suffix “AM” or “PM”, Excel will always consider the time as “AM” only. We can add the “TIME” maintained in single cell as explained by using “SUM” function.

When we add time in excel, it create a new time value. By adding 04:00 PM and 03:00 PM we will get 07:00 AM and not 07:00 PM as some of us may expect. In fact excel add the time value of both the times and then convert the decimal value of the result to desired time format; hence, we get the result of 07:00 AM and not 07:00 PM.

In excel time is a decimal value from 0 to 0.999988426 representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). To get the time value for a time we have to calculate the time elapsed in seconds from midnight (12:00:00 AM) and to divide it by total seconds for a day i.e. 86400 (24*60*60). Let’s check how we got the result as 07:00 AM by adding 04:00 PM and 03:00 PM.

As shown above, to ascertain the time value first of all we check the time elapsed from midnight and then convert the same in seconds. Finally divide it by seconds of a day i.e.86400 to get the time value. After adding both the time value we take only decimal portion and multiply it by seconds in a day then convert it in hours and we get “7”. This is the time elapsed from midnight; hence, we got sum as 07:00 AM.

We can also get time value for a time using Excel’s built-in function “TIMEVALUE”. Please do remember to mention time as text. To get the time value of 04:00 PM use the function as shown below:

=TIMEVALUE(“04:00 PM”)

To add hours, minutes and seconds to a time you can use the “TIME” function. Suppose you want to add 4 hours to the time mentioned in cell “A1”, use the below formula:

=A1+TIME(4,0,0)

To check time elapsed in hours & minutes, simply subtract the times and change the format of the cell as “[h]:mm”. However; if you try to subtract 04:00 PM from 03:00 PM you will get error shown as series of “#####” because negative time is not allowed.

If the direction of the time difference doesn’t matter we can use “ABS” function to get the desired result. Suppose, cells “A1” & “B1” contains the time 04:00 PM and 03:00 PM respectively, use below formula to get result as 1 hour: 

=ABS(B1-A1)

Sometimes, we may require to calculate the time gap in correct direction. In the above example if we subtract 04:00 PM from 03:00 PM in correct direction the answer should be 23 hours. Use the formula given below to get the time elapsed in correct direction:

=If(B1<A1, B1+1, B1)-A1

In case you have any query regarding above, you may send the same as comment to this blog.