Finding values between two dates from specific entries

[masud8956]

Hi Experts!

I have a project like this:

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]START DATE[/TD]
[TD="align: center"]1 JAN 18[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]END DATE[/TD]
[TD="align: center"]2 JAN 18[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]ENTRIES[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]ATHLETE[/TD]
[TD="align: center"]100M[/TD]
[TD="align: center"]200M[/TD]
[TD="align: center"]300M[/TD]
[TD="align: center"]400M[/TD]
[TD="align: center"]HURDLES[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]DATE[/TD]
[TD="align: center"]ATHLETE[/TD]
[TD="align: center"]100M[/TD]
[TD="align: center"]200M[/TD]
[TD="align: center"]300M[/TD]
[TD="align: center"]400M[/TD]
[TD="align: center"]HURDLES[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1 Jan 18[/TD]
[TD="align: center"]Donald[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]1 Jan 18[/TD]
[TD="align: center"]Donald[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]Martin[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]Martin[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]James[/TD]
[TD="align: center"]0:50[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]James[/TD]
[TD="align: center"]0:50[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]Donald[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]2 Jan 18[/TD]
[TD="align: center"]Donald[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]3 Jan 18[/TD]
[TD="align: center"]Steven[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:50[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]5 Jan 18[/TD]
[TD="align: center"]Eric[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]5 Jan 18[/TD]
[TD="align: center"]Steven[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]0:50[/TD]
[TD="align: center"]0:50[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]5 Jan 18[/TD]
[TD="align: center"]Eric[/TD]
[TD="align: center"]0:30[/TD]
[TD="align: center"]0:45[/TD]
[TD="align: center"]0:35[/TD]
[TD="align: center"]1:15[/TD]
[TD="align: center"]1:00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
</tbody>[/TABLE]

I have some data in range B8:H15. Column B is formatted as 'Date'. Some durations are located at D8:H15 in [h]:mm format.

At K3 and K4 I have two dates. K3 is the Start Day and K4 the End Day between which I am trying to calculate. At K5 I have found out the number of entries between these two dates using the following formula.

=COUNTIFS($B$8:$B$18,">="&K3,$B$8:$B$18,"<="&K4)

The formula returns "4" entries between these two dates as it should.

In range M8:S15 I am trying to list out the entries between those two specific days. I have used the following formula at M8:

=IFERROR(INDEX(B$8:B$18,SMALL(IF($B$8:$B$18>=$K$3,IF($B$8:$B$18<=$K$4,ROW($B$8:$B$18)-ROW($B$8)+1)),ROWS(K$3:K3))),"")

Then using the fill handle I have copied the formula to the whole destination range M8:S15. So the destination range has now 4 entry rows as it should given the Start and End date range.

WHAT I NEED NOW

I need a way so that I am able to select specific entries between those two dates. What I mean by that is, if there are multiple entries on the same day (2 and 5 Jan in the example above), I need a way to be able to list out the entries starting at 2nd entry of 2 Jan up to 2nd entry of 5 Jan. I want the Start Day (K3) and End Day(K4) to offer me options so I could pick the entry I like if there are multiple entries on the same day.

Is that even possible? I am sorry for the long post. Had to bring clarity.

Attaching the worksheet below:

https://www.dropbox.com/s/2vt49l368fhyz0u/Test1.xlsx?dl=0

Thanks in advance!

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-ge...n-two-specific-entries-between-two-dates.html

Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[masud8956]

Yes sir, I did crosspost as there was no response but I mentioned this link.

Sorry I did not post the new post link here back again...my bad!

 

[masud8956]

Hi again!

This problem is solved. I am thankful to a ForumGuru @Glenn Kennedy at Excel Forum.

https://www.excelforum.com/excel-general/1244843-finding-values-between-between-two-specific-entries-between-two-dates.html#post4971946

Anyone seeking a similar solution will be surely benefited I hope. Test files included.

Regards.