How to Calculate Total Hours in Excel (9 Easy Methods)

Method 1 – Adding Time Values to Calculate Total Hours in Excel

We have a duration list of several tasks. We will sum up the cells containing different durations.

Steps:

• Use the following formula in Cell C8.

• Here are the total hours.

• If the summation of the hour is greater than 24 hours, the formula “overflows” to the next day.

• Click on the result cell and press Ctrl + 1 to get the Format Cells dialog box.
• Go to the Number tab and choose Custom from the Category list.
• In the Type box, type [h]:mm;@.
• Click OK.

• Here’s the output.

Read More: How to Calculate Total Time in Excel

Method 2 – Calculating Total Hours Using the SUM Function

Steps:

• Insert the following formula in Cell C8.

• We will get the total duration of all the tasks.

Read More: How to Calculate the Duration of Time in Excel

Method 3 – Subtracting Time Values to Calculate Total Hours

We have a starting and finishing time for several tasks. We will calculate hours between each of the time ranges using a simple formula in Excel.

Steps:

• Enter the following formula in Cell D5.

• Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Method 4 – Calculating Total Hours If the Time Value Difference is More Than 24 Hours

For this dataset, the start time and finish time are on different dates.

• Use the following cell format for the start and finish times.

• Use the following formula in Cell D5.

• Hit Enter and use the Fill Handle tool to drag down.

• The cell format of the result column is General.

Read More: How to Sum Time in Excel

Method 5 – Finding Total Hours Between Time Records Using the IF Function

We have a list of start and end times on either the same or consecutive days, but the durations are no longer than 24 hours.

Steps:

• Use the following formula in Cell D5.

The IF function checks whether the start time is greater than the finish time. If the finish time is less than the start time, the formula adds 1 to the finish time, otherwise not. The start time is subtracted from the result of the IF formula.

• You will get the following result. Use the Fill Handle to AutoFill.

Method 6 – Applying the MOD Function to Calculate Total Hours

Steps:

• Use the following formula in Cell D5.

• Hit Enter and apply AutoFill.

Method 7 – Using the TEXT Function to Determine Total Hours in Excel

Steps:

• Use the following formula in Cell D5.

• Hit Enter and use AutoFill.

Method 8 – Applying the HOUR Function to Get Total Hours

The HOUR function returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Steps:

• Use the following formula to Cell D5.

• Use the Fill Handle (+) tool to copy the formula to the rest of the cells.

Method 9 – Using the NOW Function to Get Total Elapsed Hours

Steps:

• Apply the following formula in Cell C5.

• Hit Enter and AutoFill.

• If the time between the start time and the current time is greater than 24 hours, change the format of the resulting cell (d “days” h:mm:ss).

• Here’s the result.

Importance of Cell Formatting While Calculating Hours in Excel

Excel stores dates and times as numbers in cells. A whole number represents a day, and the decimal portion of a number represents a part of the day. For example, 1 represents 1 Jan 1900 while 1.5 represents 1 Jan 1900, 12:00 PM.

When using calculations, Excel will try to convert the date value to the number, which can look weird in results. Here’s how to convert the cell back to time.

• Select the cell and right-click on it.
• Click on the Format Cells option to bring the Format Cells dialog box. Alternatively, after selecting a cell, you can press Ctrl + 1 from the keyboard to get the dialog box.
• We have selected Cell C5 and Cell C6.

• The Format Cells dialog box will show up.
• Go to the Number tab and choose the Time from the Category list. Choose the preferred format from the Type box and click OK.

• Here’s our result.

• Alternatively, choose Custom from the Number tab, then type or choose the desired format and click OK.

• Here’s a sample result.

Download the Practice Workbook

Related Articles

• How to Calculate Lag Time in Excel
• How to Calculate Lead Time in Excel
• How to Calculate Median Follow-up Time in Excel
• How to Calculate Percentage of Time in Excel
• [Fixed!] SUM Not Working with Time Values in Excel

<< Go Back to Calculate Total Time | Calculate Time | Date-Time in Excel | Learn Excel