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.

**Read More:** How to Calculate Hours Worked Minus Lunch with Excel Formula

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

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

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

**Read More:** How to Calculate On Time Delivery Performance in Excel

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

**Read More: **How to Calculate Production per Hour in Excel

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

- Press
**ENTER**.

To get the correct value, press **CTRL+1.**

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

**Read More: **How to Calculate Billable Hours in Excel

**Download Practice Book**

## Related Articles

- 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
- Man Hours Calculation in Excel
- How to Create an Injection Molding Cycle Time Calculator in Excel

**<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel**