Add Time In Excel How To Sum Hours In Excel

How to Add Time in Excel Formula?

For example, suppose you need to know the hours and minutes one can take to complete the two tasks. First, you need to calculate by adding the total hours and minutes using the function formula that two tasks might take. Then, if the duration of completing the tasks exceeds 24 hours, one must apply a special format to the formula result.

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

Examples

Example #1

Suppose we wish to calculate the total time a student takes to complete two different assignments. For when the student can complete the first assignment in 5 hours and 40 minutes and the second assignment in 8 hours and 20 minutes:

The output is shown below.

We can see that the student took two times to complete two different assignments provided in cells: B2 and B3, and we wish to calculate the total time a student took in cell B4. So, we sum the two given times in Excel using the SUM () functionSUM () FunctionThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more. We got the desired result- 14 hours for completing two assignments. It works quickly as the given hours do not add more than 24.

Example #2

Now, let us 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:

We can see that the two times the student took to complete two different assignments are provided in cells: B2 and B3. We wish to calculate the total time the student took 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 to complete the first assignment is in a 24-hour format, i.e., 15:00 is the same as 03:00. So, to display the time as more than 24 hours,

Select “Custom,” and in the “Type” box, type: [h]: mm;@, and click “OK.”

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

Click on the “Home” tab and expand the “Number Format” dropdown. Click on “More Number Formats.” Select “Custom,” and in the “Type” box, type: [h]: mm;@, and click “OK.”

One would save this format in the ‘Type’ list the next time we need it.

Example #3

Now, let us 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:

Cell A2 contains an initial time, and the number of hours we wish to add to this is included in cell B2. Now, the addition of two-time values is made as follows:

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

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

Now, we can also achieve this 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)

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

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

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

Cell A4 contains an initial date and time. The number of hours we wish to add to this in cell B4. Now, this addition of two-time values is done as follows:

So, we see that this formula has no limitations/restrictions on the number of hours we wish to add. Hence, when 30 hours (>24) are added to the DateTime: ‘22-03-2019 17:04’, we get ‘23-03-2019 23:04.’

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

In this case, two-time values are added using the same formula 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

We can apply a Custom/Date/Time format to the formula cell if a calculated time is displayed as a decimal number.To enable to automatically format time values that, when added together, exceed 24 hours, 60 minutes, or 60 seconds, we can use the following custom number formatsCustom Number FormatsExcel custom number formatting is nothing but making the data look better or visually appealing. Excel has many inbuilt number formatting. On top of this, we can customize the Excel number formatting by changing the format of the numbers.read more and SUM () function in Excel:

For Hours – [h]:mm:ss

For Minutes – [m]:ss

For Seconds – [ss]

The Custom Hours format is already created in Excel. However, the minutes and seconds format must be made for workbooks that require them.The Custom Time formats work only for positive time values.The time format applied to a cell would only change its display presentation without changing the cell value.We can sum up the time values and then format the result as required.If a cell displays something like ##### after applying the custom formatting, then this could be because the cell is not wide enough to show the date-time value. So, one can fix this by expanding the column width in Excel Column Width In ExcelA user can set the width of a column in an excel worksheet between 0 and 255, where one character width equals one unit. The column width for a new excel sheet is 8.43 characters, which is equal to 64 pixels.read more.

How to Add Time in Excel Formula?#

Examples#

Example #1#

Example #2#

Example #3#

Things to Remember#

Recommended Articles#