Google Sheets – How to Calculate Duration from Log Events

google sheets

I have log-events with a timestamp like the following in Google Spreadsheets:

Date    Time    Event
8-30-17 8,42    Login
8-30-17 12,31   Logout
8-30-17 14,1    Login
8-30-17 18,54   Logout
8-31-17 6,89    Login
8-31-17 14,01   Logout
9-1-17  8,24    Login
9-1-17  12,57   Logout
9-1-17  13,43   Login
9-1-17  18,03   Logout

So essentially this is the date and time when a login/logout occurred. I now would like to calculate the logged in time for each date. Taking the 8-30-17 this would result in a total duration of 3,89 + 4,44 = 8,33 hours.

As a second duration I would like to get the total time between the first login and the last logout for a day. So again for the 8-30-17 this would mean a total duration of 10,12 hours.

Is it possible to calculate this with Google Spreadsheets or is this too advanced?

Best Answer

Provided not logged in over midnight (and you can handle some repetition) something like:

=sumifs(B:B,A:A,A2,C:C,"Logout")-sumifs(B:B,A:A,A2,C:C,"Login")

(copied down) may serve, assuming you adjust the delimiter (, to ;) if required and that your data is numeric.

WA109340 example

SUMIFS

Related Topic