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.

 

[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]

NAME

in or out

date_time

helper1

helper2

fred

in

01/04/2017 05:50

1

fred1

sid

in

01/04/2017 05:55

1

sid1

fred

out

01/04/2017 09:58

2

fred2

problem statement

sid

out

01/04/2017 08:20

2

sid2

fred

in

01/04/2017 10:14

3

fred3

find sid's first in and last out

sid

in

01/04/2017 08:45

3

sid3

sid

out

01/04/2017 11:45

4

sid4

easy to use offset match to find 05;55

sid

in

01/04/2017 11:51

5

sid5

so we need to find the 6th sid

fred

out

01/04/2017 14:11

4

fred4

easy to count the sids = 6

sid

out

01/04/2017 14:12

6

sid6

the helper counts how many times each name has occurred

down to and including that row

clearly we now search in column E for sid6 and return col C = 14:12

but now consider sid works the 6-2 shift on 1/4/17 and on 2/4/17

time diff

diff<.5

count

easy to use a gap of >12 hours to denote another "day"

sid

in

01/04/2017 05:55

day1

1

sid

out

01/04/2017 09:55

0.17

day1

2

easy to count the day1's and day2's

sid

in

01/04/2017 10:05

0.01

day1

3

sid

out

01/04/2017 14:05

0.17

day1

4

now offset match does the business

sid

in

02/04/2017 05:50

0.66

day2

1

sid

out

02/04/2017 08:50

0.13

day2

2

sid

in

02/04/2017 09:10

0.01

day2

3

sid

out

02/04/2017 11:50

0.11

day2

4

4

8

sid

in

02/04/2017 11:55

0.00

day2

5

01/04/2017

02/04/2017

sid

out

02/04/2017 12:50

0.04

day2

6

sid

01/04/2017 05:55

02/04/2017 05:50

sid

in

02/04/2017 13:05

0.01

day2

7

01/04/2017 14:05

02/04/2017 14:02

sid

out

02/04/2017 14:02

0.04

day2

8

once we find the 4 and the 8 the earliest and latest

times are easy to find

<colgroup><col span="2"><col span="2"><col span="5"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[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.