Google Sheets – Time Tracking Methods

google sheetsgoogle-sheets-dates

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?

Best Answer

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.

Related Topic