3

I'm using Tasker and am trying to set up a profile that would track how many hours I spend at work. I've managed to set up the tracking part, which inserts the data in a Google Spreadsheet like this:

Arrived  2017-03-11 10:15
Left     2017-03-11 12:40
Arrived  2017-03-11 13:00
Left     2017-03-11 15:50
Arrived  2017-03-11 17:30
Left     2017-03-11 21:20

What I'm wondering is: How can I add the final piece - counting the hours at my office, provided that I leave it several times per day. So for this example, the result is 9:05:00. I guess what I'm looking for is a loop, that would go trough each date, calculate differences, and add all of them into a cell.

How would I go about doing this?

6

I made a simple version of your problem you can play around with: Make a copy of the sheet

There are basically two things to do:

1. Get the duration for each visit

For that I added a Column with an arrayforumla (so we don’t have to copy it in every row) that basically looks if we are in a “Leave” row and if so, gives us the time difference between the leave and the arrival before that.

=ArrayFormula(
    IF(
        A2:A="Left";
        TO_PURE_NUMBER(B2:B) - TO_PURE_NUMBER(B1:B);
    ) 
)

(expanded for better readability

(Note: Your table needs to be well formated for that, meaning Leave and Arrival should always alternate)

Now you already got the duration for each visit.

Aggregate for each day

To make this simpler, lets add a Col to our sheet, that figures out the day without time for each row. Like this:

=ArrayFormula(IF(ISBLANK(B2:B);;INT(B2:B)))

The important part is the INT(B2:B) which converts your datetime to a date

Now I would recommend to create another sheet and automatically generate a day overview there, by simply putting a query in A1.

=query(
    Log!A1:D;
    "select C, sum(D) where A = 'Left' group by C label C 'day', sum(D) 'total time'"
)

That’s it! Here is the sheet.

  • Thanks for the answer! One question though: I keep getting "Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC" on the Daily Overview A1 field – Jernej Pangeršič Mar 12 '17 at 15:05
  • 1
    Yes I had the same problem. Forgot to mention that. The col with the duration needs to be set to Number, not Duration as a Number format. For some reasons the query can’t handle numbers in Duration format. If you still want to see it as Duration in the first table, you will need a second col, that just copies the value and is set tot Number. Its the same in my example tables, stops working if you set that row to Duration. – Marcus Blättermann Mar 12 '17 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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