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

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>
[TD="colspan: 2"]Jojo Kurien
[/TD]

</tbody>

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

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Login
[/TD]
[TD]Logout
[/TD]
[TD]Total Login Hrs
[/TD]
[/TR]
[TR]
[TD]Jojo Kurien42933
[/TD]
[TD]2017-07-17
[/TD]
[TD]22:14:07
[/TD]
[TD]23:55:00
[/TD]
[TD] 1:40:53
[/TD]
[/TR]
[TR]
[TD]Jojo Kurien42934
[/TD]
[TD]2017-07-18
[/TD]
[TD]0:02:04
[/TD]
[TD]23:09:24
[/TD]
[TD] 23:07:20
[/TD]
[/TR]
[TR]
[TD]Jojo Kurien42935
[/TD]
[TD]2017-07-19
[/TD]
[TD]1:43:41
[/TD]
[TD]23:55:33
[/TD]
[TD] 22:11:52
[/TD]
[/TR]
[TR]
[TD]Jojo Kurien42936
[/TD]
[TD]2017-07-20
[/TD]
[TD]0:03:23
[/TD]
[TD]22:34:58
[/TD]
[TD]22:31:35
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[oldbrewer]

where is 08:11:13

do you really mean if fred logs in 10pm wednesday and logs out 1 am friday, and logs in 1:30 am friday and logs out 6 am friday login is 10pm and logout is 6 am ?

 

[vmjan02]

login time for Fred will be 10pm for 2017:07:18 and logout time will be 06am for 2017:07:18 as 00:00:00 the date changes

more over Fred will be login at 10pm on 2017:07:19 and will logout by 06am on 2017:07:20 so for Fred 19th date login time will be 10pm and logout time will be 06am

I am not able to show 08:11:13 as the array don't show the time, but if required can share the excel file.

 

[oldbrewer]

where is 08:11:13 I MEANT TO TYPE.....

do you really mean if fred logs in 10pm wednesday and logs out 1 am THURSDAY, and logs in 1:30 am THURSDAY and logs out 6 am THURSDAY login is 10pm and logout is 6 am ?

 

[vmjan02]

its system generated 08:11:13, user did not need to type time need a formula or array, my earlier post has the array which is not working.

Requirement is if the user 1st time(Loing time) of date is record at 2017:07:18 22:40:00 and lasttime(Logout time) of data record at 2017:07:19 08:13:13 so output will be


Date EmpName Logintime Logouttime
2017/07/18 X 22:40:00 08:13:13 (as per the system this time is 2017:07:19) but will have the logout time for date 18 as the person is doing a night shift

 

[vmjan02]

its system generated 08:11:13, user did not need to type time need a formula or array, my earlier post has the array which is not working.

Requirement is if the user 1st time(Loing time) of date is record at 2017:07:18 22:40:00 and lasttime(Logout time) of data record at 2017:07:19 08:13:13 so output will be


Date Emp Name Logintime Logouttime
2017/07/18 X 22:40:00 08:13:13 (as per the system this time is 2017:07:19) but will have the logout time for date 18 as the person is doing a night shift

Raw data will be in Column A will have Emp Name, Column B will have date and time

Fname&Lname	Event Date
Jojo Kurien	2017-07-17 22:14:07.000

<tbody>
</tbody>

Login time as 1^st entry for 17^th date

Jojo Kurien

2017-07-18 08:05:46.000

<tbody>
</tbody>

Logout time as last time for 17^th date
Output

EmpName	Date	Login time	Logout time
Jojo Kurien	2017-07-17	22:14:07	08:05:46
Jojo Kurien	2017-07-18	22:32:10	08:08:46

<tbody>
</tbody>

Fname&Lname	Event Date
Jojo Kurien	2017-07-18 22:32:10.000

<tbody>
</tbody>

Login time as 1^st entry for 18^th date

Jojo Kurien

2017-07-19 08:08:46.000

<tbody>
</tbody>

Logout time as last time for 18^th date

 

[oldbrewer]

[TABLE="width: 1078"]
<colgroup><col span="2"><col span="2"><col span="5"><col><col><col span="3"></colgroup><tbody>[TR]
[TD]NAME[/TD]
[TD]in or out[/TD]
[TD]date_time[/TD]
[TD]helper1[/TD]
[TD]helper2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 05:50[/TD]
[TD="align: right"]1[/TD]
[TD]fred1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 05:55[/TD]
[TD="align: right"]1[/TD]
[TD]sid1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 09:58[/TD]
[TD="align: right"]2[/TD]
[TD]fred2[/TD]
[TD="colspan: 2"]problem statement[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 08:20[/TD]
[TD="align: right"]2[/TD]
[TD]sid2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 10:14[/TD]
[TD="align: right"]3[/TD]
[TD]fred3[/TD]
[TD="colspan: 3"]find sid's first in and last out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 08:45[/TD]
[TD="align: right"]3[/TD]
[TD]sid3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 11:45[/TD]
[TD="align: right"]4[/TD]
[TD]sid4[/TD]
[TD="colspan: 4"]easy to use offset match to find 05;55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 11:51[/TD]
[TD="align: right"]5[/TD]
[TD]sid5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]so we need to find the 6th sid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 14:11[/TD]
[TD="align: right"]4[/TD]
[TD]fred4[/TD]
[TD="colspan: 3"]easy to count the sids = 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 14:12[/TD]
[TD="align: right"]6[/TD]
[TD]sid6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the helper counts how many times each name has occurred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]down to and including that row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]clearly we now search in column E for sid6 and return col C = 14:12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]but now consider sid works the 6-2 shift on 1/4/17 and on 2/4/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]time diff[/TD]
[TD]diff<.5[/TD]
[TD]count[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]easy to use a gap of >12 hours to denote another "day"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 05:55[/TD]
[TD][/TD]
[TD]day1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 09:55[/TD]
[TD="align: right"]0.17[/TD]
[TD]day1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]easy to count the day1's and day2's[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]01/04/2017 10:05[/TD]
[TD="align: right"]0.01[/TD]
[TD]day1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]01/04/2017 14:05[/TD]
[TD="align: right"]0.17[/TD]
[TD]day1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]now offset match does the business[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]02/04/2017 05:50[/TD]
[TD="align: right"]0.66[/TD]
[TD]day2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]02/04/2017 08:50[/TD]
[TD="align: right"]0.13[/TD]
[TD]day2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]02/04/2017 09:10[/TD]
[TD="align: right"]0.01[/TD]
[TD]day2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]02/04/2017 11:50[/TD]
[TD="align: right"]0.11[/TD]
[TD]day2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]02/04/2017 11:55[/TD]
[TD="align: right"]0.00[/TD]
[TD]day2[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/04/2017[/TD]
[TD="align: right"]02/04/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]02/04/2017 12:50[/TD]
[TD="align: right"]0.04[/TD]
[TD]day2[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]sid[/TD]
[TD="align: right"]01/04/2017 05:55[/TD]
[TD="align: right"]02/04/2017 05:50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]in[/TD]
[TD="align: right"]02/04/2017 13:05[/TD]
[TD="align: right"]0.01[/TD]
[TD]day2[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/04/2017 14:05[/TD]
[TD="align: right"]02/04/2017 14:02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sid[/TD]
[TD]out[/TD]
[TD="align: right"]02/04/2017 14:02[/TD]
[TD="align: right"]0.04[/TD]
[TD]day2[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]once we find the 4 and the 8 the earliest and latest[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]times are easy to find[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[vmjan02]

thanks a ton, this great but share the sample with formula, as did tried but no luck.
Offsetmatch is not working as desired.

 

[oldbrewer]

in the top table you use offset match to find the first sid and go 2 cols across to get the date_time
strip out the time from that
count how many sid's = 6
concatenate sid & 6
(you do this for each sid as you are counting from top of table to current row)
now search col E for sid6 and go left 2 columns

lower table is all ins and outs for sid over 2 days
by looking at the gap between each pair you find a gap greater than 0.5 which marks the start of the second day
a helper column indicates day1 and day2
the second helper is a sequential counter which resets on day change
you can see the 4 and the 8
1 must be the start time
4 is the end time
the row below 4 is the day2 start time
8 is the day2 end time

apply this logic to your own data

are there numerous names clocking in and out every day eg fred sid ann tom ???

 

[vmjan02]

Yes in a day there are numerous names clocking, I have the data which have every details for every date of every emp as number of times he has done swip in and swip out.