0

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.

Improve this question
2
  • can you point out some example of desired result?
    – user0
    Commented Jul 26, 2019 at 8:27
  • Hello! I made a desired result sheet The important rows are 7,8,9. They have different ways to calculating the total time and getting the Date/Time In and Date/Time Out entries.
    – jnsm1138
    Commented Jul 26, 2019 at 9:50

1 Answer 1

Reset to default
0 

=ARRAYFORMULA(IF(LEN(A2:A), IF(DAY(B2:B)=DAY(D2:D), E2:E-C2:C, ("24:00:00"-C2:C)+E2:E), ))

0

Improve this answer
4
  • Thanks for this, it solves part of the problem. But I was also looking for advice on how the query formula would display the part of the table that deals with date/time outs when the day is on the separate day. For example, on row 7 the user logs in on January 16 but logs out at January 17. My current query formula uses max and min values for a single day to log it proper. This probably won't work anymore when dealing with a entry that spans more than a day
    – jnsm1138
    Commented Jul 28, 2019 at 7:48
  • you could use: =ArrayFormula(QUERY(QUERY({RAW!A2:D,DATEVALUE(RAW!C2:C),TIMEVALUE(RAW!C2:C)}, "select Col1,min(Col5),min(Col6),max(Col5),max(Col6) where Col1 is not null group by Col1,Col5", 0), "offset 1", 0)) but that wont cut it coz the real issue is that you need to differentiate between cases when person comes in today and leaves tomorrow and case when comes in today leaves today comes in tomorrow and leaves tomorrow. i.sstatic.net/VBa91.png --- i.sstatic.net/cyNrf.png
    – user0
    Commented Jul 28, 2019 at 11:19
  • 1
    Thank you very much for your help!
    – jnsm1138
    Commented Aug 1, 2019 at 7:28
  • @jnsm1138 webapps.stackexchange.com/help/someone-answers
    – user0
    Commented Aug 29, 2019 at 0:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.