How to Add Hours, Minutes, and Seconds in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

how to add hours minutes and seconds in excel


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

formula to add hours minutes and seconds in excel

  • Now, press the Enter key and you should get the added time.

formula to add hours minutes and seconds in excel

  • 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

formula to add hours minutes and seconds in excel

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)

the TIME function to add hours minutes and seconds in excel


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)

the TIME function to incrementally add hours minutes and seconds in excel

  • 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.


Related Articles

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo