# Calculate hours worked but not limited to a single day

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.

• can you point out some example of desired result? – user0 Jul 26 '19 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 Jul 26 '19 at 9:50

``=ARRAYFORMULA(IF(LEN(A2:A), IF(DAY(B2:B)=DAY(D2:D), E2:E-C2:C, ("24:00:00"-C2:C)+E2:E), ))`` • 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.stack.imgur.com/VBa91.png --- i.stack.imgur.com/cyNrf.png – user0 Jul 28 '19 at 11:19