+ Reply to Thread
Results 1 to 21 of 21

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

  1. #1
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

    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!
    Last edited by Masud8956; 09-10-2018 at 08:49 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

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



  3. #3
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    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!
    Last edited by Masud8956; 09-10-2018 at 12:05 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    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.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 09-10-2018 at 01:52 PM. Reason: typo correction

  5. #5
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    @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!
    Last edited by Masud8956; 09-11-2018 at 06:59 AM. Reason: typo correction

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    Try this... any closer??
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    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: 
    Last edited by Masud8956; 09-11-2018 at 08:55 AM. Reason: missed a point

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    It is perfect!

    Thank you very much again!

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    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.

  11. #11
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    Quote Originally Posted by Glenn Kennedy View Post
    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!
    Last edited by Masud8956; 09-11-2018 at 02:23 PM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

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

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    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.

  14. #14
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    It is great to know that you have been to Bangladesh before! Actually I worked pretty close to you (Jessore) in 2013. You must be knowing that the fish flights originate from that part of the country from Jessore. Now residing at Dhaka Cantonment. I am working at Bangladesh Air Force as a pilot.

    Yeah, probably nothing much to cheer about Dhaka. I know exactly what you mean. Your hotel (probably Hotel Pan Pacific Sonargaon) is located at one of the most crowded place. Glad to know you have had the opportunity to visit the beautiful countryside.

    Small world! Who knew we would meet at Excel Forum! Thanks for sharing your experience. If you ever come to this direction again, please leave a line or two. I will be glad to meet.

    (Worksheet wise.......please disregard my last 2 requests. I think it is best the way it is now. No need to go for VBA to solve insignificant issues)

    God Bless!

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

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

  16. #16
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    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!
    Last edited by Masud8956; 09-13-2018 at 02:50 AM. Reason: typo correction

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

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

  18. #18
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    It's great actually!

    Worked perfectly.

    Thanks and regards!!

  19. #19
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    In fact, my VBA skill is even lesser than non-existent!

    There was no problem in the test file.

    But when I am trying to add the code at my intended sheet as a Private Sub, a list pops up asking me to name the macro. If I don't name and click run it automatically selects the name one of the previous macro (General Module) and botha macro runs simultaneously.

    How can I add this code only to affect my targetted sheet?

  20. #20
    Registered User
    Join Date
    12-03-2017
    Location
    Bangladesh
    MS-Off Ver
    2016
    Posts
    23

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

    Well....I made it work. I should not have pressed the Play (Run) button.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rank entries based on values and dates
    By sebroa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2017, 03:07 PM
  2. [SOLVED] How do I return specific values from table with multiple entries
    By BigSpeekaz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2014, 03:31 PM
  3. Grouping entries by quarter & specific dates ?
    By andyzz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2014, 12:03 AM
  4. Finding difference in dates for repeat entries
    By strikinglyfamiliar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-27-2012, 03:21 PM
  5. Finding a date in a specific column surounded by other columns with similar dates
    By Freakazoid in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2012, 05:28 AM
  6. Finding values between entries in Hlookup
    By Bob Pritchard in forum Excel General
    Replies: 4
    Last Post: 01-11-2012, 01:47 PM
  7. counting unique entries for a specific date period in a series of dates
    By woody382 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-16-2010, 01:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1