Add Time in Excel

How to Add Time in Excel Formula?

We can add time in excel using the SUM () function, or do individual addition using the ‘+’ operator. However, in addition, Excel ignores the hours that exceed 24. When this is the case, we use Custom Number Format.

Add Time in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Add Time in Excel (wallstreetmojo.com)

Examples

You can download this Sum Hours Excel Template here – Sum Hours Excel Template

Example #1

If we wish to calculate the total time taken by a student to complete two different assignments, when the student can complete the first assignment in 5 hours and 40 minutes, and the second assignment in 8 hours and 20 minutes:

How to Sum Hours in Excel Example #1

The output is shown below.

How to Sum Hours in Excel example 1

We can see that the two times were taken by the student to complete two different assignments provided in cells: B2 and B3, and we wish to calculate the total time taken by a student in cell B4. So, we sum the two given times in excel using the SUM () function, and we get the desired result- 14 hours for completion of two assignments. It works quite quickly as the given hours do not add up to more than 24.

Example #2

Now, let’s say in the above example we wish to sum up more than 24 hours, where the time taken by the student to complete the first assignment is 15 hours and the time taken to complete the second assignment is 12 hours and 30 minutes:

How to Sum Hours in Excel example 1-2

The output is shown below.

How to Sum Hours in Excel example 1-3

We can see that the two times taken by the student to complete two different assignments are provided in cells: B2 and B3, and we wish to calculate the total time taken by the student in cell B4. So, we add the two given time values using excel SUM () function, and we get the desired result- 3:30. The time taken to complete the first assignment is in a 24-hour time format, i.e., 15:00 is the same as 03:00. So, to display the time as more than 24 hours, we change its format by following the below steps:

  1. Click on the ‘Home’ tab and expand the ‘Number Format’ dropdown and

  2. Click on ‘More Number Formats’


    How to Sum Hours in Excel example 1-5

  3. Select ‘Custom’ and in the ‘Type’ box, type: [h]: mm;@, and click ‘OK.’


    How to Sum Hours in Excel example 1-6

    Example 1-7

We can see that with this format, the result changes from 03:30 to 27:30.

This format would be saved in the ‘Type’ list the next time we need it.

Example #3

Now, let’s say we wish to add the desired time interval in excel to a given time: To do this, we divide the number of hours, minutes, or seconds by the number of the corresponding unit in one day (24 hours, 1440 minutes, 86400 seconds) and add the resultant quotient to the given time:

Case #1: When the time to be added is under 24 hours in excel:

Example 1-8

We can see that cell A2 contains an initial time, and the number of hours we wish to add to this is contained in cell B2. Now, the addition of two-time values is done as follows:

=Initial Time + ( No of hours to be added/24)

So we see that when 4 hours are added to the time: 12:00, we get the result as 16:00, which is true.

Now, this can also be achieved by using the TIME () functionTIME () FunctionTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more as follows:

=Initial Time + TIME ( No of hours to be added,0,0)
Example 1-9

So we see that we get the same result by using the TIME () function as well.

However, the TIME () function can be used this way only when we wish to add under 24 hours.

Case #2: When the time to be added is over 24 hours in Excel:

Example 1-10

We can see that cell A4 contains an initial DateTime, and the number of hours we wish to add to this is contained in cell B4. Now, this addition of two-time values is done as follows:

=Initial Time + ( No of hours to be added/24)

So we see that this formula has no limitations/restrictions to the number of hours we wish to add. Hence, when 30 hours (>24) are added to the DateTime: ‘20-03-2019 16:49’, we get the result as ‘21-03-2019 22:49’.

Case #3: When the excel time to be added is over 60 minutes or 60 seconds:

In this case, the addition of two-time values is done by using the same formula as above. The only difference in the formula is that:

  • the number of minutes that we wish to add to the given DateTime is divided by 1440 when the time to be added is over 60 minutes (as 1 day has 1440 minutes), and the formula works as follows:
=Initial Time + ( No of minutes to be added/1440)
  • the number of seconds that we wish to add to the given DateTime is divided by 86400 when the time to be added is over 60 seconds (as 1 day has 86400 seconds), and the formula works as follows:
=Initial Time + ( No of seconds to be added/86400)

Things to Remember

For Hours – [h]:mm:ss

For Minutes – [m]:ss

For Seconds – [ss]

Recommended Articles

This has been a guide to Add time in Excel. Here we discuss how to Sum Hours, Minutes, and Seconds in Excel and practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *