1

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?

1

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

1

Regarding your other question, you might try:

=ArrayFormula(max(if((A:A=A2)*(C:C="Logout"),B:B)))-ArrayFormula(min(if((A:A=A2)*(C:C="Login"),B:B)))

WA109340 example for *other* Q

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.