Best formula to count login - logout time

[Akbarov]

Hey Dear community,

i have big list of users login and logout time, i need to count users work time, i use minifs and maxifs to find login and logout time minifs-maxifs=result , is there any other ( easy ) way to do this?

Names are in D:D and times are in J:J

 

[mikerickson]

Could you give us a small sample of your data, with the desired results. I would expect that you had 3 columns Name, TimeIn and TimeOut.

Would the interval from log in to log out slop over midnight? Would the interval perhaps be over a day?

 

[Akbarov]

Hi mikerickson ,

Yes that's the problem, most of them starts at 8:00 and finish at 6:00 but some slop over midnight , and second problem is login and logout time are in same column J:J

can you tell me easy way to share table here? Column B is date , D names and E is Department

 

[Akbarov]

any kind of help

 

[mikerickson]

How can one tell whether an entry is log in or log out? Where do you want the inverval?
Can you give us a small sample of some data, including lay-out, and the desired results?

The over midnight can be solved with if you have a log in time in W1 and a log out time in W2 =MOD(1+W2-W1, 1) will return the interval between login and logout.

 

[Akbarov]

nevermind over midnight, they are only few, i need only first log in and last log out difference

 

[mikerickson]

Can you give us a small sample of some data, including lay-out, and the desired results?

 

[Akbarov]

check this link, i didn't find any other way to share http://alqoritm.com/table.html

 

[Akbarov]

http://alqoritm.com/table.xlsx

 

[Akbarov]

any help please