In this tutorial, I am going to show you 4 effective ways to calculate billable hours in Excel. You can use these methods even in large datasets to find out the billable hours and total fees for different clients. Throughout this tutorial, you will also learn some important Excel tools and techniques that will be very useful in any Excel-related task.

**Table of Contents**hide

## How to Calculate Billable Hours in Excel: 4 Effective Ways

We have taken a concise dataset to explain the steps clearly. The dataset has approximately **6 **rows and **5 **columns. Initially, we formatted all the cells containing dollar values in **Accounting **format. For all the datasets, we have **5 **unique columns which are **Client, Task, Hours, Hourly Rate, **and **Total**. Although we may vary the number of columns later on if that is needed.

### 1. Using SUM Function

In this method, we will see how to use **the SUM function** in Excel to calculate billable hours. Follow the steps below to do this.

**Steps:**

- First, go to cell
**D10**and insert the following formula:

`=SUM(D5:D9)`

- Then press
**Enter**and insert the below formula inside cell**F5**:

`=D5*E5`

- Again press
**EnterÂ**and copy this formula down using**Fill Handle**. - Next, navigate to cell
**F10**and type the following formula:

`=SUM(F5:F9)`

- Finally, press
**Enter**to confirm and this will give you the total bill as well as the total hours.

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

### 2. Utilizing SUMPRODUCT Function

**The SUMPRODUCT function** in Excel basically multiplies a range of numbers and then sums all of them. We can use this function to calculate billable hours in Excel. Let us see how to do this.

**Steps:**

- To begin with, double-click on cell
**F10**and enter the below formula:

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

- Next, press the
**Enter**key and you should get the values of the total bill with the total hours.

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

### 3. Calculating Billable Hours by AutoSum Feature

The **AutoSum **feature is very handy in performing basic mathematical operations. Let us see how to apply this to calculate billable hours in Excel.

**Steps:**

- To begin this method, double-click on cell
**F5**and insert the formula below:

`=PRODUCT(D5,E5)`

- After that, press the
**Enter**key and copy this formula to the cells below using**Fill Handle**. - Now, click on cell
**F10**and go to the**Formulas**tab above the ribbon. - Then, under this tab click on
**AutoSum**.

- Lastly, press the
**Enter**key on the keyboard and this is how you can get the total hours and total bills.

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

### 4. Applying VBA Code

If you are familiar with Excel **VBA**, then you can calculate billable hours with just a few clicks. The following steps show how to achieve this.

**Steps:**

- For this method, go to the
**Developer**tab and select**Visual Basic**.

- Now, select
**Insert**in the**VBA**window and click on**Module**.

- Next, type in the formula 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
```

- Then, open the macro from the
**Developer**tab by clicking on**Macros**.

- Now, 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 inside 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 and time out for an employee. Below are the detailed steps to do this.

**Steps:**

- For this, select the cells from
**E5**to**E10**and go to the**Home**tab then to**Number**. - Here from the drop-down select
**Number**.

- Now, double-click on cell
**E5**and insert the formula below:

`=(D5-C5)*24`

- Then, press the
**Enter**key 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. Also, we can calculate the exact overtime amount in hours. See the steps below to do this.

**Steps:**

- To start this method, navigate to cell
**E5**and type in the following formula:

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

- Next, press
**Enter**and immediately this will evaluate the overtime value in cell**E5**. - Finally, copy this formula to all the cells below using
**Fill Handle**.

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

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to calculate billable hours in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.