Retrive First and last occurrence between specific time intervals

[xs4amit]

Hi Experts,

I have data like the table below, where I want to fetch time (Column C) when the Emp Code 843567 swiped his card on 05/23/2018 for first and last time between time interval 6:00 AM and 3:00 PM.


[TABLE="width: 500"]
<tbody>[TR]
[TD]EMP Code[/TD]
[TD]DATE[/TD]
[TD]CARD SWIPE TIME[/TD]
[/TR]
[TR]
[TD]843567[/TD]
[TD]05/23/2018[/TD]
[TD]6:10 AM[/TD]
[/TR]
[TR]
[TD]674593[/TD]
[TD]05/23/2018[/TD]
[TD]5:45 AM[/TD]
[/TR]
[TR]
[TD]843567[/TD]
[TD]05/23/2018[/TD]
[TD]8:51 AM[/TD]
[/TR]
[TR]
[TD]365745[/TD]
[TD]05/23/2018[/TD]
[TD]6:20 AM[/TD]
[/TR]
[TR]
[TD]845593[/TD]
[TD]05/23/2018[/TD]
[TD]5:55 AM[/TD]
[/TR]
[TR]
[TD]843567[/TD]
[TD]05/23/2018[/TD]
[TD]2:43 PM[/TD]
[/TR]
[TR]
[TD]843567[/TD]
[TD]05/23/2018[/TD]
[TD]5:51 PM[/TD]
[/TR]
[TR]
[TD]674597[/TD]
[TD]05/23/2018[/TD]
[TD]6:40 AM[/TD]
[/TR]
[TR]
[TD]843567[/TD]
[TD]05/24/2018[/TD]
[TD]6:00 AM[/TD]
[/TR]
[TR]
[TD]674597[/TD]
[TD]05/24/2018[/TD]
[TD]10:00 AM[/TD]
[/TR]
[TR]
[TD]674593[/TD]
[TD]05/24/2018[/TD]
[TD]6:12 AM[/TD]
[/TR]
[TR]
[TD]674593[/TD]
[TD]05/24/2018[/TD]
[TD]11:00 AM[/TD]
[/TR]
</tbody>[/TABLE]



Which should give me result exactly like 6:10 AM and 2:43 PM

Right now I am trying something like =IFERROR(INDEX(Range,Match(1,(Test 1)*(Test 2)*(Test 3),0),3),"") SHIFT+CRL+ENTER
But not working

Thanks in advance

 

[xs4amit]

Quick response is highly appreciated

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=TEXT(INDEX($C$2:$C$13,MATCH(E2,IF($B$2:$B$13=F2,IF($C$2:$C$13>="06:00 AM"+0,IF($C$2:$C$13<="03:00 PM"+0,$A$2:$A$13))),0)),"h:mm AM/PM")&" and "&TEXT(LOOKUP(9.99999999999999E+307,1/(($A$2:$A$13=E2)*($B$2:$B$13=F2)*($C$2:$C$13>="06:00 AM"+0)*($C$2:$C$13<="03:00 PM"+0)),$C$2:$C$13),"h:mm AM/PM")


where E2 = 843567 and F2 = 5/23/2018.