Nazmul Hossain Shovon
In this tutorial, I am going to share with you 3 simple methods to add hours, minutes, and seconds in excel. Whether you are working with employee worksheets or students’ attendance sheets, these methods will come in handy. Also, through this tutorial, you will learn some valuable excel tools and formulas which will be helpful in other tasks as well.
Download Practice Workbook
You can download the practice workbook from here.
3 Simple Methods to Add Hours, Minutes, and Seconds in Excel
We have taken a concise dataset for this tutorial to explain the concepts clearly. This dataset has 2 columns and 6 rows. The first column has the original time and the second column will show the time after the addition is complete. Also, you need to apply a Time format to all the cells from B5 to C10.
1. Use of Excel Formula
We can use simple excel formulas to add hours, minutes, and seconds in excel. In this method, you have to manually type the dividing formula. Let us see how to do this.
1.1 Add Hours
Steps:
- First, go to cell C5 and enter the following formula:
=B5+1/24
- Now, press the Enter key and you should get the added time.
- Finally, copy this formula to the cells below using Fill Handle.
1.2 Add Minutes
We can add minutes to the existing time following the same previous procedures. But to get an additional minute we need to divide by 1440 instead of 24. Go to cell C5 and insert the formula as in the image below.
=B5+1/1440
Now, copy the formula to all the cells below to add minutes to all the time cells.
1.3 Add Seconds
To add seconds in excel instead of hours or minutes, we have to divide by 86400. For this, navigate to cell C5 and type in the following formula:
=B5+1/86400
Next, press the Enter key and this will add a second to the existing time.
Read More: How to Add Hours to Time in Excel (8 Quick Ways)
2. Use of Excel TIME Function
The TIME function in excel allows creating a time by specifying individual components like hours, minutes, and seconds. This function is also useful to add to the existing time. This is especially true if you have to assemble this function inside another formula. Note that the individual time components have to be integers and not decimals.
2.1 Add Hours
To add an additional hour, insert the formula below in cell C5:
=B5+TIME(1,0,0)
Now, press Enter and copy this formula to the cells where you want to add hours.
2.2 Add Minutes
If we want to minutes to our existing time using the TIME function, we can use the following formula.
=B5+TIME(0,1,0)
2.3 Add Seconds
We can easily add seconds to the existing time by modifying the previous formula. You can see the modification needed in the image below.
=B5+TIME(0,0,1)
Now, if we press Enter and copy this formula to the other cells, we will get the desired results.
Read More: How to Add Minutes to Time in Excel (3 Quick Methods)
3. Adding Time Incrementally
We can use the TIME function to incrementally add hours, minutes, and seconds in excel. We will use the example below to see how to add 2 minutes incrementally. You can then use this formula to add hours and seconds also.
Steps:
- To begin with, select all the cells from B6 to B10.
- Then, navigate to cell B6 and type in the following formula:
=B5+TIME(0,2,0)
- Now, press Ctrl+Enter key, and this will add the times incrementally to the cell you selected.
Read More: How to Add Time in Excel Automatically (5 Easy Ways)
Things to Remember
- Remember to format the time cells as Time before inserting any data otherwise they won’t work.
- If you want to format the time difference, you can do that by right-clicking on the required cells and selecting Format Cells.
- If you get any #NUM! error, then it may be because you entered a negative number.
- You might also get a #VALUE! Error if you enter a non-numeric value.
Conclusion
I hope that you were able to apply the above methods to add hours, minutes, and seconds in excel. These methods can be very useful when you are working with large datasets. Among these methods, you should choose the one that best suits your needs. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
