In this tutorial, we will demonstrate 4 effective ways to calculate billable hours in Excel. We’ll use the following dataset to explain the steps. All the cells containing dollar values are formatted in **Accounting **format.

### Method 1 – Using SUM Function

We can use **the SUM function** to calculate billable hours.

**Steps:**

- Go to cell
**D10**and insert the following formula:

`=SUM(D5:D9)`

- Press
**Enter**and insert the below formula in cell**F5**:

`=D5*E5`

- Press
**Enter**and copy this formula down using the**Fill Handle**. - Go to cell
**F10**and enter the following formula:

`=SUM(F5:F9)`

- Press
**Enter**to return the total bill as well as the total hours.

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

### Method 2 – Using SUMPRODUCT Function

**The SUMPRODUCT function** in Excel multiplies a range of numbers and then sums all of them.

**Steps:**

- Double-click on cell
**F10**and enter the below formula:

`=SUMPRODUCT(D5:D9,E5:E9)`

- Press
**Enter**to return the values of the total bill with the total hours.

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

### Method 3 – Using AutoSum Feature

**Steps:**

- Double-click on cell
**F5**and insert the formula below:

`=PRODUCT(D5,E5)`

- Press
**Enter**and copy this formula to the cells below using the**Fill Handle**. - Click on cell
**F10**and go to the**Formulas**tab above the ribbon. - Click on
**AutoSum**.

- Press
**Enter**to return the total hours and total bill.

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

### Method 4 – Using VBA Code

**Steps:**

- Go to the
**Developer**tab and select**Visual Basic**.

- Select
**Insert**in the**VBA**window and click on**Module**.

- Enter the code below in the new window:

```
Public Sub BillableHours()
Range("D10").Value = Application.WorksheetFunction.Sum(Range("D5:D9"))
Range("F10").Value = Application.WorksheetFunction.SumProduct(Range("D5:D9"), Range("E5:E9"))
End Sub
```

- Open the macro from the
**Developer**tab by clicking on**Macros**.

- In the
**Macro**window, select the**BillableHours**macro and click**Run**.

As a result, the **VBA **code will calculate the total hours and total bill in the respective cells.

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

## How to Calculate Man Hours in Excel?

We can find the man hours in Excel by using the time in and time out for an employee.

**Steps:**

- Select the cells from
**E5**to**E10**. - Go to the
**Home**tab, then to**Number**. - From the drop-down list, select
**Number**.

- Double-click on cell
**E5**and insert the formula below:

`=(D5-C5)*24`

- Press
**Enter**and copy the formula to the cells below using the**Fill Handle**.

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

## How to Make an Overtime Calculator in Excel?

We can use **the IF function** in Excel to calculate whether an employee has worked overtime or not, and to calculate the exact overtime amount in hours.

**Steps:**

- Go to cell
**E5**and enter the following formula:

`=IF((D5-C5)*24>$E$10,((D5-C5)*24)-$E$10,0)`

- Press
**Enter**to return the overtime value in cell**E5**. - Copy this formula to all the cells below using
**Fill Handle**.

**Read More:** Excel Formula for Overtime over 8 Hours

**Download Practice Workbook**

**Related Articles**

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