Render to Excel Formulas Listing

How to Calculate Hours Worked – Excel & Google sheets

Download Case Workbook

Download the example workbook

In this Article

  • Summate Hours with a Simple Formula
    • Calculating Overtime
  • Modern Part
  • Calculate Hours Worked in a Weekly Timesheet
    • Calculate Overtime
    • Calculate Regular Hours
  • Summate Hours Worked in a Weekly Timesheet past Projection
  • Calculating Hours Worked in Google Sheets

This tutorial will demonstrate how to calculate hours worked in Excel and Google Sheets.

calculate hours worked Main Function

Summate Hours with a Simple Formula

We can calculate the hours worked by deducting the terminate fourth dimension from the starting time fourth dimension.   The information has to be entered into Excel in a correct time format for the formula to piece of work.

TimeSheet 1st

The formula D3-C3 volition give us the percent of the day worked based on 24 hours in a day.  To get the hour worked, we need to multiply this value by 24.

Dates and Times are stored as numbers in Excel.   If we were to modify the format of the start and end time columns in the above graphic to a full general number format, we would get the numeric equivalents of the times shown.

Format Change

Computing Overtime

We can apply the same blazon of formula to calculate overtime.

TimeSheet with Overtime

In the case above, the standard fourth dimension is 8 hours.

The post-obit formula calculates the Normal time worked in the day.

If the employee has worked more than 8 hours, the formula will only render a maximum of viii hours.

To calculate the overtime, we can go the residuum of the hours worked using this formula below:

Modernistic Function

If the stop time is before the start time, this may be due to a night shift being worked.  To solve the trouble of the formula returning a negative number, we employ the Mod function.

TimeSheet using MOD Function

As with the uncomplicated formula, nosotros need to multiply the value calculated past the Modern office by 24 to become the hours worked.

Calculate Hours Worked in a Weekly Timesheet

We tin use the IF, SUM and MAX Functions to calculate the hours worked in a weekly broken down by regular fourth dimension and overtime.

Weekly TimeSheet

Calculate Overtime

The overtime is calculated in one case a person works more than xl hours a week.

The kickoff part of the first range of the SUM office is an absolute, while the second part is not.  As yous copy this formula down to the bottom of the table, you volition discover that the SUM role adds upward all the Hours worked in cavalcade E.  Every bit the SUM range is increased, so the hours worked is increased.  One time the SUM gets to more than than 40 hours, Overtime hours are put into the Overtime column as an increasing total.

Calculate Regular Hours

The regular hours are calculated based on the total hours, and the overtime worked.

We use the MAX function so that nosotros do not end upward with Negative hours where the Employee has worked overtime as if the consequence returns a negative, then the MAX function volition return a zero.

Summate Hours Worked in a Weekly Timesheet by Project

Taking the calculation in a higher place i step further, nosotros tin can divide the hours worked by the Project that the employee worked on using the SUMIF Role

Weekly Time Sheet Project

The SUMIF function will sum the Regular hours  in column F according to the criteria selected in cavalcade H – in the example of the above formula, it will look for the ABC Projection and sum the Regular hours for that project.

Calculating Hours Worked in Google Sheets

All the above Excel examples work the aforementioned way in Google Sheets.

calculate hours worked Google Function