Adding and subtracting time in Excel is something that people often times have a difficult time figuring out how to do correctly. Often times you are doing it right, but the results don’t seem to be displaying correctly. Other times you may give up doing it the “correct” way and set up an hours column and a minutes column. Then add the minutes, divide by 60, and add it to the total hours.
If you don’t want your head to hurt by doing it that way, there’s a much easier way to add and subtract time in Excel.
Adding Time in Excel
Adding time is just as easy as adding any other number in Excel using the SUM function. Simply populate your list with the times formatted as hours:minutes like this 4:25 for four hours and 25 minutes.
Next, to add the time simply click the ∑ AutoSum button in the Editing Group on the Home tab while in the cell where you want the total to be. Make sure the correct cells are included in the AutoSum, and hit Enter. In the screenshot below, I wanted the total in cell B5, so I clicked that cell then clicked AutoSum. After checking that it correctly added cells B2:B4 I hit Enter, which gave me my total of 15 hours and 48 minutes.
Now, what happens if we are adding times that exceed 24 hours? We end up with a messy result if we don’t do a bit of formatting first.
In the same example as the screenshot above, if I make Job 1 take 14:25 instead of 4:25, the AutoSum changes to 1:48. Clearly that is not correct.
In order to get it to display the correct total (25:48), we need to format the total time. To do this, right click the total cell and select Format Cells.
In the Category box to the left, select “Custom”. Then in the Type box to the right, enter [h]:mm.
By using the left and right square brackets around the letter “h”, you are telling Excel that it can exceed 24 hours in the display.
The previous examples showed how to get the aggregate amount of time. But what if you wanted to add an amount of time to a given time of day?
Say we were trying to figure out what time of day it would be 8 hours after say 9 AM. With the TIME function, we can add any number of hours, minutes, and seconds to a given time to get the time it would be after that length of time.
In this case we would have 9AM in one cell and add TIME(8,0,0) to it. The numbers in the parenthesis are 8 for the number of hours we want to add, the first 0 for the number of minutes, and the last 0 for the number of seconds.
You can change the numbers in the TIME portion of the formula to get any number of hours, minutes, or seconds to add to another time this way.
Read on to learn about subtracting time in Excel.