Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-languagegs-vlookup

Calculate duration of work and break period of employees from time sheet data in Google Sheets

Have two Google sheets timesheet (data is appended from an RFID reader) https://docs.google.com/spreadsheets/d/1T_ZbPub9CV_-S5Ve5OxtlPoGfvV8uXkxxoKnUGBuXPo/edit?usp=sharing would like to have a duration of employee worked and break period in a month. (employee can take multiple breaks in a day) for a given day always first punch is IN and the second is OUT which would give the duration of time he has worked. the rest would be break period

result sheet (monthly attendance) https://docs.google.com/spreadsheets/d/1IWu1eJCtG3LQQYfF2N5Hd02rZ2t3GfgbjOoYQbQuQFk/edit?usp=sharing

As the data is one column duno how to achieve iteration for a given day.

monthly attendance Result expected

Solution

  • first, you will need some prep work. see columns J:O:

    0

    then you can do simple HLOOKUP:

    =ARRAYFORMULA(IFERROR(HLOOKUP(A24:24, QUERY(QUERY({Sheet2!J1:O}, 
 "select Col1,day(Col1),sum(Col5),sum(Col6),Col4 
  where month(Col1)+1="&B23&" 
  group by Col1,Col4 
  format Col1'd'", 0), 
 "select Col5,"&IF(D23="Break", "sum(Col4)", "sum(Col3)")&" 
  where Col5 is not null
  group by Col5
  pivot Col2
  label Col5'Employee names'", 1), ROW(A2:A20), 0)))

    0

    spreadsheet demo

    UPDATE:

    to fix the missing punch-out you can add a check:

    ={"punch-out check"; 
 ARRAYFORMULA(IFNA(VLOOKUP(A2:A&C2:C, QUERY(QUERY({A2:A&C2:C, 
 COUNTIFS(A2:A&C2:C, A2:A&C2:C, ROW(A2:A), "<="&ROW(A2:A))},
 "select Col1,sum(Col2) group by Col1"),
 "where not Col2 >1 offset 1", 0), 2, 0)))}

    1

    then the formula would be:

    =ARRAYFORMULA({"Date","Start", "End", "Name";
 {ARRAY_CONSTRAIN(FILTER(SORT(FILTER({timesheet!A2:B, timesheet!E2:E}, timesheet!F2:F<>1), 1, 1, 3, 1, 2, 1), 
 MOD(ROW(INDIRECT("timesheet!A1:A"&ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F, 1))), 2)), 
 ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F, 1), 2)}, 
 QUERY(FILTER(SORT(FILTER({timesheet!A2:B, timesheet!E2:E}, timesheet!F2:F<>1), 1, 1, 3, 1, 2, 1), 
 MOD(ROW(INDIRECT("timesheet!A2:A"&ROWS(timesheet!A2:A)-COUNTIF(timesheet!F2:F, 1)+1)), 2)), "select Col2, Col3", 0)})

    9

    and dashboard would list it like:

    ={"Didn't punched out"; FILTER(timesheet!E2:E, timesheet!A2:A=C2, timesheet!F2:F=1)}

    9