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

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.


Download Practice Workbook

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


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:

Importance of Cell Formatting While Calculating Hours in Excel

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.

Importance of Cell Formatting While Calculating Hours in Excel

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

Importance of Cell Formatting While Calculating Hours in Excel

  • Eventually, get the following result.

Importance of Excel Cell Format While Calculating Total Hours

  • Similarly, you can custom 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.


9 Methods to Calculate Total Hours in Excel

1. Add Time Values to Find 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.

Add Time Values to Find Total Hours in Excel 

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

Steps:

  • First, type the below formula in Cell C8.
=C5+C6+C7

Add Time Values to Find Total Hours in Excel 

  • Consequently, the following will be the total hours.

Add Time Values to Find Total Hours in Excel 

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.

Add Time Values to Find Total Hours in Excel 

In such cases, you have to change the format of the cell which is showing 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, 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.

Related Content: How to Add Minutes to Time in Excel (5 Easy Ways)


2. Calculate Total Hours Using Excel SUM Function

Previously, in Method 1, we have 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)

Calculate Total Hours Using Excel SUM Function

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


3. Subtract 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.

Subtract Time Values to Calculate Total Hours

Steps:

  • Type the below formula in Cell D5 at first.
=C5-B5

Subtract Time Values to Calculate Total Hours

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


4. Get Total Hours If Time Value Difference is More Than 24 Hours

In Method 2, we have 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.

Get Total Hours If Time Value Difference is More Than 24 Hours

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

Get Total Hours If Time Value Difference is More Than 24 Hours

Steps:

  • First, type the following formula in Cell D5.
=(C5-B5)*24

Get Total Hours If Time Value Difference is More Than 24 Hours

  • 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: Excel Formula for Overtime over 40 Hours [with Free Template]


5. Find 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

Find Total Hours Between Time Records Using Excel IF Function

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.


6. Apply 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)

Apply Excel MOD Function to Calculate Total Hours

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.


7. Use 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")

Use TEXT Function to Determine Total Hours in Excel

  • Consequently, here is our result.

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


8. 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)

Excel HOUR Function to Get Total Hours

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


9. Excel 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

Excel NOW Function to Get Total Elapsed Hours (Certain Time to Till Now)

  • Subsequently, here is our result.

Excel NOW Function to Get Total Elapsed Hours (Certain Time to Till Now)

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.

Related Content: Excel formula to calculate hours worked & overtime [with template]


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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo