How to Calculate Billable Hours in Excel (4 Effective Ways)

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.


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.

how to calculate billable hours in excel


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)

sum function to calculate billable hours in excel

  • 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)

sumproduct function to calculate billable hours in excel

  • 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


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)

autosum feature to calculate billable hours in excel

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

vba code to calculate billable hours in excel

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

insert module to calculate billable hours in excel

  • 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

vba code to calculate billable hours in excel

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

How to Calculate Man Hours in Excel

  • 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)

How to Make an Overtime Calculator in Excel

  • 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

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo