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