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

The following sample dataset will be used for illustration.

Method 1 – Calculate the Total Hours Worked in a Week Using the Basic Method

Select cell F5 and enter the following formula:

=SUM(E5-D5)

Formula Explanation

SUM(E5-D5) represents the individual working hours for Sunday.

Press ENTER to get the working hours for Sunday.
Use Fill Handle to AutoFill the formula for the remaining cells.

Select cell F11 and enter the following formula:

=F5+F6+F7+F8+F9

Formula Explanation

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

Press ENTER. The total hour will be in general format which is not the correct format.

To get the correct number, press CTRL+1 to open the dialog box:

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

After clicking on the OK button, the correct total hours worked in that particular week will be displayed.

Method 2 – Calculate the Total Hours Worked in a Week Using the SUM Function

Select cell F11.

Enter the following formula:

=SUM(F5:F9)

Formula Explanation

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

Press ENTER. The total hours worked in a week is incorrect.

Press CTRL+1.

Open Name Tab >> go to Custom >> select [h]:mm:ss >> click OK. The value will be corrected.

Method 3 – Calculate the Total Hours Worked in a Week Using the AutoSum Function

Select cell F11.

Open Formulas Tab >> go to AutoSum >> select Sum.

All cells in the range of F5: F10 having numbers before cell F11 will automatically be selected.

Press ENTER. The sum of Total Hours Worked in a Week is incorrect.

To get the correct value, press CTRL+1.

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

Method 4 – Calculate the Total Hours Worked in a Week Using the TEXT & SUM Function

Select cell F11.

Enter the following formula:

=TEXT(SUM(F5:F9),”[h]:mm:ss”)

Formula Explanation

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

Press ENTER to get the total hours worked in a week.

Method 5 – Calculate the Total Hours Worked in a Week Using the 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.

In column B, we have two different projects, Project A and Project B. To calculate the total hours worked against Project A. We will use conditional SUMIF function for this.

Select cell F16.

Enter the following formula:

=SUMIF(B5:B14,E16,F5:F14)

Formula Explanation

=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.“