I'm trying to automate getting the number of hours worked for a session, not a day. My raw data is the following:
Here is a sample spreadsheet for the data
I was able to create a formatted version using the following code:
=arrayformula(query({RAW!A2:A,DATEVALUE(RAW!C2:C),TIMEVALUE(RAW!C2:C)}, "Select Col1, Col2, min(Col3), max(Col3) where Col1 is not null group by Col1, Col2 order by Col1 label Col1 'Name', Col2 'Date', min(Col3) 'In', max(Col3) 'Out'"))
For now, I get the number of hours worked by just getting the difference between the two times (the MAX and the MIN)
The problem arises when users log out early the following day (12AM or 1AM). Since the current formula only detects the entries for a single day, It cannot accurately calculate the persons number of hours worked.
Best Answer