Search code examples
datetimegoogle-sheetssumarray-formulasgoogle-sheets-query

How to split hours in a timesheet to account for working after midnight?

I'm using geolocation to automatically record my work hours. But, as I often work after midnight, I need to automatically "split" the hours into each day they were worked. The final result desired is just the hours worked each day, per row Jan 1st - 2 hours

For example: if I start work on the 1st/Jan, and work 10pm to 2am, then two hours should be billed to the 1/Jan and the other two hours to 2/Jan.

Below is example automatically generated data, based on when I physically enter the work area.

Example Data

So, one possible solution would be to transpose this list horizontally, and add logic that says "if not exited by midnight, then add entries "exited: 23:59" & "entered: 00:00" the next day)

In this case, the next step might look like:

Possible Solution

But I don't know how to transpose(?) the data, or automatically generate the missing fields, in Google Sheets. Or perhaps there is a better solution?

Editable copy of spreadsheet here

Solution

  • try:

    =ARRAYFORMULA(QUERY(IFERROR({DATEVALUE(F2:F), G2:G}), 
 "select Col1,sum(Col2) 
  where Col2 is not null 
  group by Col1 
  label sum(Col2)''"))

    0

    E2:

    =ARRAYFORMULA(QUERY({QUERY(
 IFERROR(SPLIT(SUBSTITUTE(FILTER(B1:B, A1:A="entered"), " at ", "♦"), "♦")), 
 "select Col1+Col2 label Col1+Col2''"); IFERROR(
 IF(INDEX(SPLIT(SUBSTITUTE(FILTER(B:B, A:A="exited"), " at ", "♦"), "♦"),,1)<>
 INDEX(SPLIT(SUBSTITUTE(FILTER(B1:B, A1:A="entered"), " at ", "♦"), "♦"),,1), 
 INDEX(SPLIT(SUBSTITUTE(FILTER(B1:B, A1:A="entered"), " at ", "♦"), "♦"),,1)+
 "23:59:59.999",))}, "where Col1 is not null order by Col1", 0))

    F2:

    =ARRAYFORMULA(QUERY({TRANSPOSE(QUERY(TRANSPOSE(SPLIT(
 SUBSTITUTE(FILTER(B:B, A:A="exited"), " at ", "♦"), "♦")),,99^99))*1; IFERROR(
 IF(INDEX(SPLIT(SUBSTITUTE(FILTER(B:B, A:A="exited"), " at ", "♦"), "♦"),,1)<>
 INDEX(SPLIT(SUBSTITUTE(FILTER(B:B, A:A="entered"), " at ", "♦"), "♦"),,1), 
 INDEX(SPLIT(SUBSTITUTE(FILTER(B:B, A:A="entered"), " at ", "♦"), "♦"),,1)
 +"23:59:59", ))}, "where Col1 is not null order by Col1", 0))

    G2:

    =ARRAYFORMULA(IF(F2:F="",,F2:F-E2:E))