Google Sheets – Calculate Work Hours

formulasgoogle sheetsgoogle-sheets-dates

Given work hours for each weekday (let's say 9:00:00-17:00:00 Monday through Friday), a start timestamp (e.g., 1/15/2019 6:15:15) and end timestamp (e.g., 1/21/2019 14:35:10)…

How can I get the total work hours between start and end, excluding all other hours (i.e., weekends, 17:00:01-8:59:59)?

I'm in need of a basic, reusable formula that does the following for many cases:

Start: 1/15/2019 6:15:15
End: 1/21/2019 14:35:10
Work Hours: 37:35:10

Note again here that I don't want all hours for each day counted, only those within the work hours for each day.

NETWORKDAYS sadly does not give the exact decimal needed.

Best Answer

=NETWORKDAYS(TO_DATE(A2), TO_DATE(B2)) * (E2-D2) -
 IF(INDEX(SPLIT(A2, " "), 1, 2) > D2, D2 - INDEX(SPLIT(A2, " "), 1, 2), ) -
 IF(INDEX(SPLIT(B2, " "), 1, 2) < E2, E2 - INDEX(SPLIT(B2, " "), 1, 2), )

column A and column B formatted as Date time
column D and column E formatted as Time
column C formatted as Duration

Related Solutions

Google-sheets – Automatically calculate overtime hours

You can do this by modifying the current formulas and adding one of your own.

Based on the template you mentioned, you need to amend the formula for the Total hours per day: column to:

=IF(24*(C2-B2) > 10, 10, 24*(C2-B2))

Then in Column E add a new formula:

=IF(D2=10, ABS(10.00 - (24*(C2-B2))),0)

Then the Formula for Total Hours For This Pay Period needs to be amended to include any hours over the 10 hour threshold.

=SUM(D2:D16)+SUM(E2:E16)

This will give you an output similar to the this: Google Spreadsheet Hours Worked

Google-sheets – How to prepare data to be summarized with a pivot table

Basically you have A, B and C columns as headers and the pair of columns with the data to be summarized. Use the array notation to build an array with the required data structure. In order to be able to include new form submissions, use references of the following form A2:C.

Add the following formula to the cell A1 of a new sheet.

=QUERY(
   {
     A1:C1,"Case Number","Hours";
     A2:C,D2:E;
     A2:C,F2:G;
     A2:C,H2:I;
     A2:C,J2:K
   },
   "Select * where Col1>0"
 )

A1:C1,"Case Number","Hours" is a row with the array headers. A2:C,D2:E and the other similar are a set of five columns, the first have first Case Numbers and hours column pair, the second the second column pair and so on.

Reference

Using arrays in Google Sheets - Google Docs editors Help

Best Answer

Related Solutions

Google-sheets – Automatically calculate overtime hours

Google-sheets – How to prepare data to be summarized with a pivot table

Reference

Related Topic