excel - Calculate Time Tracking from log file

Question

I have a log file that stores login and logout of users session.my users are login and logout multiple time in a day. now i want to calculate how much time my user was online in each day or week.

this is a sample of my log file:

  DATE & Time   Status
1/25/2021 22:45 LOGIN
1/25/2021 7:49  LOGOUT
1/25/2021 4:16  LOGIN
1/25/2021 3:16  LOGOUT
1/24/2021 21:33 LOGIN
1/24/2021 7:38  LOGOUT
1/24/2021 5:37  LOGIN
1/24/2021 4:47  LOGOUT
1/24/2021 1:28  LOGIN
1/24/2021 1:23  LOGIN
1/23/2021 21:58 LOGIN
1/23/2021 13:09 LOGOUT
1/23/2021 11:41 LOGIN
1/23/2021 8:54  LOGOUT
1/23/2021 8:48  LOGIN
1/23/2021 5:54  LOGOUT
1/23/2021 1:06  LOGIN
1/22/2021 21:54 LOGIN
1/22/2021 21:53 LOGOUT
1/22/2021 21:46 LOGIN

csv

is it possible to calculate online time in excel?

Best Regards.

i've attached my sample file (i converted it to xlsx)

https://www.sendspace.com/file/40rwg5

question from:https://stackoverflow.com/questions/65918960/calculate-time-tracking-from-log-file

Answer 1

You can use the following formula if the data meets some of the prerequisites:

1. Login / Logout must be in pairs
2. In terms of time Login must be before Logout
3. Login / Logout must be within a day

I edited your sample data to get correct result.

To get list of dates use array formula:

=IFERROR(INDEX(INT($A$2:$A$21),MATCH(1,--(COUNTIF($D$1:D1,INT($A$2:$A$21))=0),0)),"")

Array formula after editing is confirmed by pressing ctrl + shift + enter

to calculate hours use:

=SUMPRODUCT($A$2:$A$21*(D2=INT($A$2:$A$21))*("logout"=$B$2:$B$21)-$A$2:$A$21*(D2=INT($A$2:$A$21))*("login"=$B$2:$B$21))