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

`=C5+C6+C7`

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

`=SUM(C5:C7)`

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

`=C5-B5`

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

`=(C5-B5)*24`

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

`=IF(B5>C5,C5+1,C5)-B5`

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

`=MOD(C5-B5,1)`

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

`=TEXT(C5-B5,"[hh]:mm")`

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

`=HOUR(C5-B5)`

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

`=NOW()-B5`

- 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**