Find a Date&Time between two Dates&Time

[vmjamshad]

Dear All,
I want to find out a time which is greater than a time and less than another time. Please see the below data:

[TABLE="width: 273"]
<tbody>[TR]
[TD]Date & Time[/TD]
[TD]Transaction Type[/TD]
[/TR]
[TR]
[TD]03/01/16 8:38:36[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]03/01/16 18:36:02[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]04/01/16 8:12:28[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]04/01/16 17:23:44[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]06/01/16 8:14:30[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]06/01/16 19:17:40[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]07/01/16 8:14:24[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]07/01/16 18:13:54[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]08/01/16 10:02:36[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]08/01/16 14:50:34[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]10/01/16 8:22:12[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]10/01/16 19:51:24[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]11/01/16 8:22:56[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]11/01/16 17:23:26[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]12/01/16 8:07:42[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]12/01/16 21:38:04[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]13/01/16 8:02:40[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]13/01/16 18:20:44[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]14/01/16 8:03:46[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]17/01/16 8:06:18[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]17/01/16 16:23:14[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]17/01/16 16:27:36[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]17/01/16 18:55:54[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]18/01/16 8:03:42[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]18/01/16 16:15:34[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]19/01/16 7:58:48[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]19/01/16 18:17:46[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]20/01/16 12:58:06[/TD]
[TD]I[/TD]
[/TR]
</tbody>[/TABLE]

Here, I stands for IN and O for OUT. I need to find next 'OUT time' after 'IN time' - 04/01/16 8:12:28 and before next 'In Time'- 06/01/16 8:14:30. The result will be 04/01/16 17:23:44.

Thanks in advance. - Jamshad

 

[admiral100]

Hi,

D2 contain the first IN and E2 contain the second IN

Ctrl + Shift +Enter NOT just Enter

F2 =IFERROR(INDEX($A$2:$A$29,LARGE(($A$2:$A$29>$D$2)*($A$2:$A$29<$E$2)*("O"=$B$2:$B$29)*(ROW($A$2:$A$29)-1),1)),"")


[TABLE="width: 693"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date & Time[/TD]
[TD]Transaction Type[/TD]
[TD][/TD]
[TD]First I[/TD]
[TD]second I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03/01/16 08:38[/TD]
[TD]I[/TD]
[TD][/TD]
[TD]04/01/16 08:12[/TD]
[TD="align: right"]06/01/16 08:14[/TD]
[TD="align: right"][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106, align: right"]04/01/16 17:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]03/01/16 18:36[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/01/16 08:12[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04/01/16 17:23[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]06/01/16 08:14[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]06/01/16 19:17[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]07/01/16 08:14[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]07/01/16 18:13[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]08/01/16 10:02[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]08/01/16 14:50[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]10/01/16 08:22[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10/01/16 19:51[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]11/01/16 08:22[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]11/01/16 17:23[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]12/01/16 08:07[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]12/01/16 21:38[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]13/01/16 08:02[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]13/01/16 18:20[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]14/01/16 08:03[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]17/01/16 08:06[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]17/01/16 16:23[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]17/01/16 16:27[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]17/01/16 18:55[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]18/01/16 08:03[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]18/01/16 16:15[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]19/01/16 07:58[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]19/01/16 18:17[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]20/01/16 12:58[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[vmjamshad]

Thanks, But I am looking for VBA Code.

 

[vmjamshad]

Any Views