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

1
votes

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

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-query-languagegs-vlookup
This looks difficult because I can't see how you tell whether they're signing in or signing out – Tom Sharpe
pls add some example of desired output – player0
@TomSharpe for any given day the first is IN and the second is OUT. Thanks have add this info in description now – Kanted
@player0 have edited and add the desired result output. – Kanted
@kanted Did one of these answers help you do what you wanted (tally the worked time for an employee)? If so, upvote /accept. Suddenly requiring error handling is not something that should be asked after already receiving answers IMO. You could ask a new SO question for that case. – tehhowch

2 Answers

1
votes

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

1
votes

I think you can do it as 

Sum of even times - Sum of odd times

=ArrayFormula(sumif(iferror(iseven(rank(if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,$B$1,O$2)),timesheet!$B2:$B),if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,$B$1,O$2)),timesheet!$B2:$B),1)),false),true,timesheet!$B2:$B)-
sumif(iferror(isodd(rank(if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,$B$1,O$2)),timesheet!$B2:$B),if((timesheet!$E2:$E=$A3)*(timesheet!$A2:$A=date(2019,$B$1,O$2)),timesheet!$B2:$B),1)),false),true,timesheet!$B2:$B))

Here's how it looks on the whole sheet:

enter image description here

EDIT

With a check that number of punches in/out is even:

=ArrayFormula(if(isodd(countifs(timesheet!$E$2:$E,$A3,timesheet!$A$2:$A,date(2019,$B$1,O$2))),"Error",sumif(iferror(iseven(rank(if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,$B$1,O$2)),timesheet!$B$2:$B),if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,$B$1,O$2)),timesheet!$B$2:$B),1)),false),true,timesheet!$B$2:$B)-
sumif(iferror(isodd(rank(if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,$B$1,O$2)),timesheet!$B$2:$B),if((timesheet!$E$2:$E=$A3)*(timesheet!$A$2:$A=date(2019,$B$1,O$2)),timesheet!$B$2:$B),1)),false),true,timesheet!$B$2:$B)))

enter image description here