How To Calculate Work Hours In Excel
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.
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.
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.
Computing Overtime
We can apply the same blazon of formula to calculate 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 ( ( D3-C3 )*24 > $ H $ 13 , $ H $ 13 , ( D3-C3 )*24 ) |
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:
=IF ( ( D3-C3 )*24 > $ H $ 13 , ( ( D3-C3 )*24 )-$ H $ 13 , 0 ) |
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.
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.
Calculate Overtime
The overtime is calculated in one case a person works more than xl hours a week.
=IF ( SUM ( $ E $ iv : E4 ) > 40 , SUM ( $ Eastward $ 4 : E4 )-40 , 0 ) |
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
=SUMIF ( $ H $ 4 : $ H $ 10 , H4 , $ F $ 4 : $ F $ 10 ) |
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.
Source: https://www.automateexcel.com/formulas/calculate-hours-worked/
Posted by: cooperappermak.blogspot.com
0 Response to "How To Calculate Work Hours In Excel"
Post a Comment