Finding values between between two specific entries (between two dates)

Hi everyone!

I sought help at MrExcel a couple of days ago about this but not getting any response.

HTML Code: 

The worksheet attached below:
HTML Code:
https://www.dropbox.com/s/2vt49l368f...est1.xlsx?dl=0

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.
Code:

Please Login or Register  to view this content.

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 only. I have used the following formula at M8:
Code:

Please Login or Register  to view this content.

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 worksheet attached 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?

Can anyone please help? Thanks in advance!

Are there going to be rules about whch one is going to be selected? If so, what are those rules?

Hi @Pepe Le Mokko,

Sorry this post can be deleted

.....I am wondering why!

Hi @Glenn!

Thanks for your reply!

Actually, there is not going to be any rules. I just want a drop down to appear when I select K3 and K4 if those dates include multiple entries.

For example: As per my attached worksheet, If I select "Start Date" at K3 as 01 Jan 18 and "End Date" at K4 as 03 Jan 18, things are simple as both the dates have one entry only. But If I select "Start Date" at K3 as 02 Jan 18 and "End Date" at K4 as 05 Jan 18, I would like a dropdown to appear at K3 and K4 as both of those dates have multiple entries (3 entries each). If the drop down works out then when I select 2nd entry of 02 Jan 18 as Start Date and 2nd entry of 5 Jan 18 as End Date, I want M8:S18 to list out those rows only.

I am not sure if that would be possible.

Regards!

I am struggling to understand what you want. It seems like you want to be presented with a range of choices... and then decide what to include/exclude on an entirely arbitrary basis. This is (probably) the best I can do. Columns J & K can be hidden to prevent them being a distraction.

Select your dates as before. Then choose from the dropdown in the yellow cells in column Q whichever ones you want to include. The remainder of the table then populates.

@Glenn,

Thank you Sir for taking the time to modify my worksheet. It is a beautiful solution. I am sorry my explanation was not clear enough.

The problem is that my project contains about 4000 input rows. With this solution I will have to manually select all the intended rows (include1, 2,3, and so on) to populate my destination range. I only need to sort the entries of 2 specific dates (Start Date and End date at O3 and O4 respectively) if they contain more that one entry on the same day (include1 and include3 in the sample file are not needed as those two dates, 01 Jan and 03 Jan have single entries).

It is true my selection of dates will be arbitrary. But none of the dates will ever include more than 4 entries. I was wondering if I could sort the duplicate entries for my input dates at O3 and O4 only. 4 rows of helper columns for O3 and 4 rows of helper columns for O4 only as there will be a max 4 entries on the same date in my whole project. And all the other dates in between should populate automatically (irrespective of single or multiple entries).

In short, I am trying to populate my destination range from a start date to an end date, with a choice to select the entry (1st,2nd,3rd or 4th) only for the start date and end date if they have more than one entry.

Please tell me if I need to provide any more information. I tried my best to explain but sorry English is not my 1st language.

I have a feeling may be I am asking a little too much, only experts like you may guide me better.

Regards!

Try this... any closer??

Wow!

Thank you very much sir! That is the solution I was looking for!

I had no idea that this kind of tricky criteria could be accommodated.

A slight problem though:
Probably the formula at Name Manager for 'Start' needs a little modification. As I have populated the input range with a few more entries, the drop down at M6 returns only empty values as I select dates towards the lower rows. (file attached below). I am not facing this problem with 'End' as the drop down is offering options minus the blank cells.

And at Q5 my original formula to count total number of entries between start and end date is:

Please Login or Register  to view this content.

This formula returns the number of all entries between two selected dates. What modification do I need at Q5 to attain the correct value after meeting the criteria at M6 and M7?

Instead of the text (Start 1,2,3) at helper 1 and 2 I would like the text (Mission 1,2,3). I changed the text 'Start' as 'Mission'. But the drop down is not responsive when I change text "End' to text 'Mission'.

I have bordered the two cells with red for your valuable suggestion.

HTML Code: 

Fixed. I forgot to fix the issue of the leading blanks in START. That should be it all now. If there are any further issues, please post the sheet on this site, not on dropbox.

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

If that's it.... You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

It would also be good manners to tell the folks at Mr Excel that your issue is now solved, to prevent anyone there wasting their time.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Originally Posted by Glenn Kennedy

It would also be good manners to tell the folks at Mr Excel that your issue is now solved, to prevent anyone there wasting their time.

Thank you for your suggestion. I will definitely share this at MrExcel.

Please pardon my ignorance. Whenever I am trying to add reputation I am getting this message:

You must spread some Reputation around before giving it to Glenn Kennedy again.

What did I do wrong? I would really like to add reputation for this post.

I am sorry to bring up the original issue yet again though, I have marked it just now.....in the latest attachment from you [Test1 (GK3).xlsx], if I select Start Day as 02 Jan 18 and End Day as 06 Jan 18, it returns zero value in whole destination range. I understand that is because there is no entry on 06 Jan. Is it possible that when I select Start Day as 02 Jan 18 and End Day as 06 Jan 18, in such cases the value between 02 and 5 Jan are returned (as 5 Jan is the closest previous day where there is an entry, or up to 4 Jan if no entry for 5 Jan and so on)

And What should I do to have Q3 and Q4 cells (dropdowns) to display blank if O3 and O4 are blank respectively?

I am extremely sorry for these late questions. Answers to this will be like a bonus for me. You have already made my project a success!

Regards!

Two questions>

1. Do you REALLY want it to bring up the nearest (previous) date if you choose a non-existing date? Is it not better to leave it blank, thus alerting you to the fact that your chosen date does not exist? Otherwise, how will you know that your chosen start/end date does not correspond to an actual value.

2. Resetting the DD's to blank, if/when you delete the selected dates will require VBA. Is that acceptable to you?

Regarding rep, you must have given some to me on a previous thread... and not given any to anyone else in between times. That's life...

As a matter of general interest, which part of Bangladesh are you in? I worked in Dhaka and in the SW (Khulna area) for about 2 months between 2009 and 2013. Beautiful countryside... maybe not Dhaka itself (except around Kawran Bazaar - my hotel was JUST beside it... AMAZING colurs, sights and smells in the market) but the rural areas really are fantastic.

I was working (indirectly) for the Bangladesh Shrimp & Fish Foundation and the Bangladesh Frozen Foods Exporters Association.

Hahaha. No. I stayed in La Vinci, Kawran Bazaar. The other one was WAYYYY out if my price range!! I ate their once.. I preferred the food in La Vinci. The guys there remembered me from one trip to the next..

Hi @Glenn!

I am sorry bringing up it again.

As some of the users of my project requesting, I would be grateful again if you could help out with the option you offered me #12.

2. Resetting the DD's to blank, if/when you delete the selected dates will require VBA. Is that acceptable to you?

It is about getting Q3 and Q4 cells (dropdowns) to display blank if O3 and O4 are blanks respectively. VBA is fine as my project is already an xlsm file.

The latest version is available at #8. I would have attached it again but the attach button here is non-responsive.

Regards!

Try it now. I hope it is OK... my VBA skills are virtually non-existent!!

Glad you got sorted. I wouldn't have had a clue what to do. However, your solution - stop doing anything - very often works for me.