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:15Left     2017-03-11 12:40Arrived  2017-03-11 13:00Left     2017-03-11 15:50Arrived  2017-03-11 17:30Left     2017-03-11 21:20What 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?
Time tracking with Google Spreadsheets
google spreadsheets
I made a simple version of your problem you can play around with:Make a copy of the sheetThere are basically two things to do:1. Get the duration for each visitFor that I added a Column with an arrayforumla (so we dont 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 dayTo 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 dateNow 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')Thats it! Here is the sheet.