How to Calculate Hours Worked – Excel & Google sheets

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

calculate hours worked Main Function

Calculate Hours with a Simple Formula

We can calculate the hours worked by deducting the end time from the start time.   The information has to be entered into Excel in a correct time format for the formula to work.

TimeSheet 1st

The formula D3-C3 will give us the percentage 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 change the format of the start and end time columns in the above graphic to a general number format, we would get the numeric equivalents of the times shown.

Format Change

Calculating Overtime

We can use the same type of formula to calculate overtime.

TimeSheet with Overtime

In the example above, the standard time is 8 hours.

The following formula calculates the Normal time worked in the day.

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

To calculate the overtime, we can get the rest of the hours worked using this formula below:

 

MOD Function

If the end time is before the start time, this may be due to a night shift being worked.  To solve the problem of the formula returning a negative number, we use the MOD function.

TimeSheet using MOD Function

As with the simple formula, we need to multiply the value calculated by the MOD function by 24 to get the hours worked.

Calculate Hours Worked in a Weekly Timesheet

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

Weekly TimeSheet

Calculate Overtime

The overtime is calculated once a person works more than 40 hours a week.

The first part of the first range of the SUM function is an absolute, while the second part is not.  As you copy this formula down to the bottom of the table, you will notice that the SUM function adds up all the Hours worked in column E.  As the SUM range is increased, so the hours worked is increased.  Once the SUM gets to more 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 we do not end up with Negative hours where the Employee has worked overtime as if the result returns a negative, then the MAX function will return a zero.

Calculate Hours Worked in a Weekly Timesheet by Project

Taking the calculation above one step further, we can divide the hours worked by the Project that the employee worked on using the SUMIF Function

Weekly Time Sheet Project

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

 

Calculating Hours Worked in Google Sheets

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

calculate hours worked Google Function

 

 

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!