While working in Excel, often we need to calculate total hours. For example, you can estimate the total hours required for a certain project from a list of the duration of different tasks of that project. Similarly, you can calculate the salaries of employees by calculating total working hours. Besides, you can subtract time values to get the hours between them. In this article, I will discuss some easy formulas and Excel functions to calculate total hours.

**Table of Contents**hide

**1. Adding Time Values to Calculate Total Hours in Excel**

In this method, we will add time values using a simple formula. For instance, we have a duration list of several tasks.

Now, we will simply sum up the cells containing different durations.

**Steps:**

- First, type the below formula in
**Cell C8**.

`=C5+C6+C7`

- Consequently, the following will be the total hours.

⏩ **Note:**

If the summation of the hour is greater than 24 hours, the above-mentioned formula will show the below result which is not what we expected.

In such cases, you have to change the format of the cell which shows the total hours.

➤ To change the cell format, click on the corresponding cell and press ‘**Ctrl **+ **1**’ to get the **Format Cells** dialog box.

➤ Next, go to the **Number** tab, and choose **Custom** from the **Category** list. Now, in the **Type** box, which is on the top of the formats list, type **[h]:mm;@ **and click** OK**.

➤ Finally, we will get the following result.

**Read More: ****How to Calculate Total Time in Excel**

**2. Calculating Total Hours Using Excel SUM Function**

Previously, in **Method 1**, we calculated total hours using a simple formula. However, now we will use **the SUM function** to calculate the total duration of different tasks.

**Steps:**

- Type the below formula in
**Cell C8**.

`=SUM(C5:C7)`

- Here, the
**SUM**function adds all the values in the range**C5:C7**. As a result, we will get the total duration of all the tasks.

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

**3. Subtracting Time Values to Calculate Total Hours**

Sometimes, we have to calculate the total hours between time values. For example, we have a starting and finishing time for several tasks. Now, we will **calculate hours between each of the time** ranges using a simple formula in Excel.

**Steps:**

- Type the below formula in
**Cell D5**at first.

`=C5-B5`

- As a consequence, you will get the following result. Use the
**Fill Handle**(**+**) tool to**copy the formula to the rest of the cells**.

**Similar Readings**

**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 Elapsed Time in Excel****How to Calculate Percentage of Time in Excel****How to Calculate Slack Time in Excel**

**4. Calculating Total Hours If The Time Value Difference is More Than 24 Hours**

In **Method 2**, we calculated the time difference between time values but, all the difference was less than 24 hours. So, when the time difference is greater than 24 hours we have to use a different formula to calculate the total hour difference. As the time difference will be greater than 24 hours, in our dataset start time and finish time are of different dates.

Remember, we have used the below cell format for the start and finish times (see the screenshot).

**Steps:**

- First, type the following formula in
**Cell D5**.

`=(C5-B5)*24`

- Hit
**Enter**and use the**Fill Handle**tool to drag down. Finally, you will get the below result.

Here, the **Number** format of the result column is **General**. That is why we got the hours as **25.5**, **26**,** 26.5**, **29,** etc. You can change the **Cell Format** as you wish. Also, if you want hours as a whole number then **the INT function** in Excel would be a great help.

**Read More:** **How to Calculate Hours and Minutes in Excel**

**5. Finding Total Hours Between Time Records Using Excel IF Function**

Till now, to get the time difference between time values, we have used simple formulas. However, if the start time is **9:00 PM** one day and the end time is **5:00 AM **the next day, a simple **subtraction formula** won’t work. In such cases, we will calculate the total hour difference using **the IF function**.

**Steps:**

- First, type the below formula in
**Cell D5.**

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

Here, 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. Finally, the start time is subtracted from the result of the **IF** formula.

- As a result, you will get the following result.

**Read More: How to Calculate Total Hours Worked in a Week in Excel**

**6. Applying Excel MOD Function to Calculate Total Hours**

Similar to **Method 4**, we can use **the MOD function** to calculate the total hours if the time difference is negative. So, let’s apply the function to our dataset.

**Steps:**

- Type the following formula in
**Cell D5**at first.

`=MOD(C5-B5,1)`

Here, the **MOD **function flips the negative values to the positive ones, and usually, this function returns the remainder after a number is divided by a divisor.

- Lastly, you will get the total time difference in hours as below.

**Read More:** **How to Calculate Hours and Minutes for Payroll Excel**

**7. Using TEXT Function to Determine Total Hours in Excel**

Now, we will use** the TEXT function** to calculate the total time difference between time values. So, here are the steps involved in this method.

**Steps:**

- Initially, type the below formula in
**Cell D5**.

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

- Consequently, here is our result.

Here, the **TEXT** function converts the subtracted result to **hh:mm **format.

**Similar Readings**

**How to Calculate Hours from Date and Time in Excel****How to Calculate Time Difference Between AM and PM in Excel****How to Calculate Difference Between Two Dates and Times in Excel****How to Calculate On Time Delivery Performance in Excel****How to Calculate Production per Hour in Excel**

**8. Applying Excel HOUR Function to Get Total Hours**

Now, we will use **the HOUR function** to get the total hours between time values. The **HOUR** **function** returns the hour as a number from **0 **(**12:00 A.M.**) to **23** (**11:00 P.M.**). So, let’s go through the steps to calculate the total hours.

**Steps:**

- Type the below formula to
**Cell D5**.

`=HOUR(C5-B5)`

- As a consequence, we get the time difference (
**8**hours). Use the**Fill Handle**(**+**) tool to copy the formula to the rest of the cells.

**Read More:** **How to Calculate Cycle Time in Excel**

**9. Using The NOW Function to Get Total Elapsed Hours (Certain Time to Till Now)**

We can calculate the total hours elapsed from a start time to the **current time** using** the NOW function** in Excel.

**Steps:**

- First, type the below formula in
**Cell C5**.

`=NOW()-B5`

- Subsequently, here is our result.

⏩ **Note:**

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

➤ In conclusion, we will get the below result.

**Similar Readings**

**How to Calculate Turnaround Time in Excel Excluding Weekends****Calculate Hours Between Two Dates and Times in Excel Excluding Weekends****How to Calculate Years of Service in Excel****How to Calculate Travel Time Between Two Cities in Excel****How to Calculate Turnaround Time in Excel**

**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, in Excel, **1** represents **1 Jan 1900**. As mentioned before, **1.5** represents **1 Jan 1900, 12:00 PM** in Excel.

So, for ease of calculation and data representation, we have to change the cell format in Excel. Changing the cell format does not alter the values, instead, it changes the way of the representation of the data. If you want to change cell formats, follow the below steps:

- First, select the cell and right-click on it. Next, 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**. I have selected**Cell C5**and**Cell C6**.

- As a result, the
**Format Cells**dialog box will show up. - Now, go to the
**Number**tab and choose the**Time**from the**Category**list. After that, choose the preferred format from the**Type**box and click**OK**.

- Eventually, we got the following result.

- Similarly, you can customize cell format. To do that, select the cells and press
**‘Ctrl**+**1**’ to bring the**Format Cells**window. Next, choose**Custom**from the**Number**tab. Then, type/choose the desired format and click**OK**.

- As a consequence, you will get a result like below.

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Conclusion**

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

**Related Articles**

**Calculate Elapsed Time Between Two Dates in Excel****Excel Calculate Hours between Two Times after Midnight****How to Calculate Average Handling Time in Excel****How to Calculate Average Response Time in Excel****How to Calculate Average Turnaround Time in Excel****How to Calculate Billable Hours in Excel****Excel Formula for Overtime over 8 Hours****How to Calculate Hours Worked and Overtime Using Excel Formula****Excel Formula to Calculate Overtime and Double Time****Excel Formula To Calculate Time Worked****How to Sum Time in Excel****[Fixed!] SUM Not Working with Time Values in Excel****Man Hours Calculation in Excel****How to Create an Injection Molding Cycle Time Calculator in Excel****How to Calculate Hours Worked Minus Lunch with Excel Formula**