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?

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);
)
)
``````

(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
• 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