Finding values between two dates from specific entries

[masud8956]

Hi Experts!

I have a project like this:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

3

START DATE

1 JAN 18

4

END DATE

2 JAN 18

5

ENTRIES

4

6

7

DATE

ATHLETE

100M

200M

300M

400M

HURDLES

DATE

ATHLETE

100M

200M

300M

400M

HURDLES

8

1 Jan 18

Donald

0:45

0:35

1:00

0:30

1:15

1 Jan 18

Donald

0:45

0:35

1:00

0:30

1:15

9

2 Jan 18

Martin

1:00

0:45

0:30

1:15

0:35

2 Jan 18

Martin

1:00

0:45

0:30

1:15

0:35

10

2 Jan 18

James

0:50

0:45

1:15

1:00

0:30

2 Jan 18

James

0:50

0:45

1:15

1:00

0:30

11

2 Jan 18

Donald

0:30

0:45

1:00

1:15

0:35

2 Jan 18

Donald

0:30

0:45

1:00

1:15

0:35

12

3 Jan 18

Steven

1:15

0:50

1:00

0:35

0:45

13

5 Jan 18

Eric

1:00

1:15

0:30

0:45

0:35

14

5 Jan 18

Steven

0:45

0:30

1:15

0:50

0:50

15

5 Jan 18

Eric

0:30

0:45

0:35

1:15

1:00

16

17

18

<tbody>
</tbody>

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.