1st Login time and Last logout time

[vmjan02]

Hi,

I have the array formula which gives the 1st entry with respect to date and emp name

1st Entry = {=1/MAX((D4=Raw!$M$2:$M$21063)*(Raw!$B$2:$B$21063<>0)*(1/Raw!$B$2:$B$21063))} Array
last entry = {=MAX((Raw!$M$2:$M$21063=D4)*(Raw!$B$2:$B$21063))} Array

		Jojo Kurien
Empname&date	Date	Login	Logout	Total Login Hrs
Jojo Kurien42917	2017-07-01	0:00:03	8:13:24	8:13:21
Jojo Kurien42918	2017-07-02	8:13:38	16:24:20	8:10:42
Jojo Kurien42919	2017-07-03	13:49:06	22:02:27	8:13:21
Jojo Kurien42920	2017-07-04	13:59:36	22:35:37	8:36:01

<tbody>
</tbody>

Above result goes all well with array, but many emp have night shift as show below and array don't work here

		Login	Logout	Total Login Hrs
Jojo Kurien42933	2017-07-17	22:14:07	23:55:00	1:40:53
Jojo Kurien42934	2017-07-18	0:02:04	23:09:24	23:07:20
Jojo Kurien42935	2017-07-19	1:43:41	23:55:33	22:11:52
Jojo Kurien42936	2017-07-20	0:03:23	22:34:58	22:31:35

<tbody>
</tbody>

Here user have logout at 2017 -07-18 08:11:13 now this entry should have captured on logout time for 2017-07-17
then the user will come on 2017-07-18 22:17:10 now this will be login time for 18th and logout time for 18th will be 2017-07-19 8:10:11

Any idea. It should be always 1st entry and last entry.

 

[vmjan02]

Thank a ton, logic worked all good. thanks once again.

 

[oldbrewer]

you are most welcome.............