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

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.

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.

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.

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.

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.

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: