How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)

You often need to find out the total hours of work in a week or in a month of your subordinates in your organization. In this article, I will show you how to calculate the total hours worked in a week in Excel. To represent the information I have taken 7 columns; these are Name, Weekday, Entry Time, Exit Time, and Working Hours.

how to calculate total hours worked in a week in excel


Download Practice Book


Top 5 Methods to Calculate Total Hours in a Week in Excel

In this article, I am going to show you the top 5 methods to calculate the total hours worked in a week in Excel.


1. Calculate Total Hours Worked in a Week Using Basic Method

In this method, we will discuss the basic method to calculate the total hours worked in a week in Excel. But before that, we need to find out the working day in each of the weeks. And to do so, we will apply the SUM function here and follow the following steps:

  • First, select cell F5 and put the following formula:
=SUM(E5-D5)
Formula Explanation

Here, SUM(E5-D5) represents the individual working hour for Sunday.

how to calculate total hours worked in a week in excel

  • Then, click ENTER and get the working hour for Sunday.how to calculate total hours worked in a week in excel
  • After that, use Fill Handle to AutoFill the formula for the rest of the cells to get the working hour for other working days in Excel.

how to calculate total hours worked in a week in excel

Now it is the time to start the main operation. For this, select cell F11 and input the following formula:

=F5+F6+F7+F8+F9
Formula Explanation

Here, =F5+F6+F7+F8+F9 represents the total hours worked in that particular week.

Formula Explanation Here, =F5+F6+F7+F8+F9 represents the total hours worked in that particular week.

  • Now click ENTER and the total hour in a general form which is not correct.

To get the correct number, we need to apply a keyboard shortcut, CTRL+1 to open the dialogue box and follow the following steps:

  • Open Name Tab >> go to Custom >> select [h]:mm:ss >> click OK

Immediately after clicking on the OK button, there appears the total hours worked in that particular week.

how to calculate total hours worked in a week in excel

Read More: How to Calculate Hours and Minutes in Excel (7 Handy Ways)


2. Calculate Total Hours Worked in a Week Using SUM Function

We can also calculate total hours worked in a week in Excel easily by using the SUM function. For this, we need to follow the following steps:

  • First, select cell F11.

how to calculate total hours worked in a week in excel

  • Then, put the following formula:
=SUM(F5:F9)
Formula Explanation

Here, SUM(F5:F9) represents the total working hours of ADAM in that particular week in between the range of F5 and F9.

how to calculate total hours worked in a week in excel

 

  • Now, click ENTER and get the total hours worked in a week, which appears in general form and is not correct as well.

There, you need to perform the same process using the dialogue box mentioned in the previous method to get the correct total hours worked in a week.

how to calculate total hours worked in a week in excel

Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends


3. Calculate Total Hours Worked in a Week Using AutoSum Function

We can use the AutoSum function too to calculate the total hours worked in a week in Excel. For this, we need to follow the following steps:

  • First, select cell F11.

  • Then, you need to follow some necessary steps.
  • Open Formulas Tab >> go to AutoSum >> select Sum

how to calculate total hours worked in a week in excel

Immediately after selecting the Sum option, we can see that all cells in the range of F5: F10 having numbers before cell F11 are automatically selected.

how to calculate total hours worked in a week in excel

  • Now, click ENTER and get the total hours worked in a week, which appears in general form and is not correct as well.

There, you need to perform the same process using the dialogue box mentioned in the previous method to get the correct total hours worked in a week.

Read More: Excel Calculate Hours between Two Times after Midnight (3 Methods)


Similar Readings


4. Calculate Total Hours Worked in a Week Using TEXT & SUM Function

In the previously discussed method, we have faced a problem not getting the total number directly. That’s why now I am going to discuss another convenient method to calculate total hours worked in a week in Excel using the TEXT function, where we find the value without any further formatting.

  • Let’s select cell F11

  • Now you have to type the entire formula:
=TEXT(SUM(F5:F9),”[h]:mm:ss”)
Formula Explanation

Here, TEXT(SUM(F5:F9) represents the numeric value of (SUM(F5:F9) in text form, and ”[h]:mm:ss” represents the format like which we want it to appear.

how to calculate total hours worked in a week in excel

  • Finally, click ENTER and get the total hours worked in a week in Excel.
    how to calculate total hours worked in a week in excel

Read More: How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)


5.  Calculate Total Hours Worked in a Week Using SUMIF Function

This is an additional method to calculate the total hours worked in a week in Excel, where you need to find the value of maintaining some particular criteria. Here in column B, we have two different projects, Project A and Project B. And we need to calculate the total hours worked against Project A in Excel. To do so, we are going to apply a conditional SUMIF function here and for this, we need to follow the following steps.

  • First, select cell F16.

how to calculate total hours worked in a week in excel

  • Then you need to apply the following value:
=SUMIF(B5:B14,E16,F5:F14)
Formula Explanation

Here, =SUMIF(B5:B14,E16,F5:F14) represents the sums only the values lying in the range F5:F14, where the corresponding cells in the range C5:C14 equal “Project A.

how to calculate total hours worked in a week in excel

  • Finally, click ENTER and get the total hours worked in a week in Excel.

Finally, you need to perform the same process using the dialogue box mentioned in the previous method to get the correct total hours worked in a week.

Read More: Excel formula to calculate hours worked & overtime [with template]


Practice Book

I’ve given a practice sheet in the workbook to practice these explained ways to calculate total hours worked in a week in Excel. You can download it from the link given above.

how to calculate total hours worked in a week in excel


Conclusion

In this article, I have tried to explain 5 different ways to calculate the total hours worked in a week in Excel. Note that, you can also calculate total hours worked in a month or in a year too in Excel by just following the same methods. Last but not least, I will be extremely grateful if you comment down below any of your suggestions, ideas, or feedback.


Related Articles

Foyez

Foyez

Hello everyone, This is Foyez Alam. I have recently completed my MBA from Institute of Business Administration (IBA), University of Dhaka. Earlier I have completed my B.Sc in Leather Engineering from University of Dhaka. Now I am working as an Excel and VBA Content Developer in SOFTEKO. Through patience and hard work, I have a passion for learning new things and applying my knowledge to new situations. That's why I am here. Besides this, I love to interact with different people and I love to spend my spare time reading, watching movies, dancing, etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo