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 which will be very useful in any excel related task.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 4 Effective Ways to Calculate Billable Hours in Excel

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:** **[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)**

### 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:** **Excel Formula To Calculate Time Worked**

**Similar Readings**

**How to Calculate Time Difference in Excel (13 Ways)****How to Calculate Production per Hour in Excel (4 Ways)****Excel Calculate Hours between Two Times after Midnight (3 Methods)****How to Calculate Average Turnaround Time in Excel (4 Methods)****How to Calculate Hourly Rate in Excel (2 Quick Methods)**

### 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 Total Hours in Excel (9 Easy Methods)**

### 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 Use Time Format in Excel VBA (Macro, UDF, and UserForm)**

## 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**Fill Handle**.

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

## 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:** **How to Calculate Overtime over 40 Hours Using Excel Formula**

## 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. Lastly, to learn more **excel** techniques, follow our **ExcelDemy** website. If you have any queries, please let me know in the comments.

**Related Articles**

**How to Add Minutes to Time in Excel (5 Easy Ways)****How to Calculate Hours Worked Minus Lunch with Excel Formula****Excel Timesheet Formula with Lunch Break (3 Examples)****How to Calculate Average Response Time in Excel (4 Methods)****How to Calculate Average Handling Time in Excel (2 Easy Ways)****Excel Formula to Calculate Overtime and Double Time (3 Ways)**