# 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:

• Go to cell E5 and enter the following formula:
`=IF((D5-C5)*24>\$E\$10,((D5-C5)*24)-\$E\$10,0)`

• Press Enter to return the overtime value in cell E5.
• Copy this formula to all the cells below using Fill Handle.

## Related Articles

<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF