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
<tbody>
</tbody>
Above result goes all well with array, but many emp have night shift as show below and array don't work here
<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.
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.