+ Reply to Thread
Results 1 to 15 of 15

List All Dates Between Two Dates and Vlookup

  1. #1
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Thumbs up List All Dates Between Two Dates and Vlookup

    Hello Guys,

    I am trying to list all dates between two dates "First day of absence and Last day of absence" (Leave Record Sheet)Excluding weekends in Sheet 1 column A and its associated "Type of Leave" in Column B.
    thank you for being patient with me.

    Regards,
    Attached Files Attached Files
    Last edited by ismailshajji; 11-12-2019 at 10:08 AM. Reason: Solved

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,646

    Re: List All Dates Between Two Dates and Vlookup

    I don't have time right now to apply this to your file, but I've attached a file that I did for someone else with a similar query - names and date ranges which needed to be expanded to individual days (in columns J and K). Maybe you can take something from this.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Thanks Pete will look into this.

  4. #4
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Sorry i am a bit lost .

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,646

    Re: List All Dates Between Two Dates and Vlookup

    I'm going out shortly, but I'll take a more detailed look this afternoon. What I was trying to convey in that attachment is that you need a few helper columns - it isn't just some super formula in column A that will give you the individual dates.

    Pete

  6. #6
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Thanks Pete for your prompt respose.

    I am trying to Lookup values in April 19 sheet from the Column A and B of sheet Leave Record Sheet and return the Value from Coulum D. if the dates falls under Bank holiday then the formula sholuld look in the sheet UK Bank Hols
    and return the value of Column1 if its a weekend then the formula should say weekend or just empty cell.

    I found the formula to do this
    Please Login or Register  to view this content.
    but iam having difficulty in implementing it using if function and vlookup.
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,646

    Re: List All Dates Between Two Dates and Vlookup

    I started applying the file that I attached to your first attachment before I went out, i.e. before your later attachment, so I thought I might as well continue with that - see the results in Sheet1. The formulae have all been copied down to row 23 (as the hyphens indicate), but you might want to copy them further if you have more data. Columns A to D can be hidden if you want the sheet to look like your original.

    I have done what you asked for in your first post in this thread, i.e. expanded dates from the date ranges in the Leave Record Sheet, but I have not implemented missing out weekends or Bank Holidays. An easy way of doing this is by ensuring that your date ranges only cover the working week (i.e. from Monday to Friday), and avoiding Bank Holidays in the date ranges.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42
    Quote Originally Posted by Pete_UK View Post
    I started applying the file that I attached to your first attachment before I went out, i.e. before your later attachment, so I thought I might as well continue with that - see the results in Sheet1. The formulae have all been copied down to row 23 (as the hyphens indicate), but you might want to copy them further if you have more data. Columns A to D can be hidden if you want the sheet to look like your original.

    I have done what you asked for in your first post in this thread, i.e. expanded dates from the date ranges in the Leave Record Sheet, but I have not implemented missing out weekends or Bank Holidays. An easy way of doing this is by ensuring that your date ranges only cover the working week (i.e. from Monday to Friday), and avoiding Bank Holidays in the date ranges.

    Hope this helps.

    Pete
    Thank you so much pete for your help and your time.
    I have realised after joining this Forumn that Humanity is still alive by the people like you and people still have time for others with out expecting anyting in return God Bless you my Friend.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,646

    Re: List All Dates Between Two Dates and Vlookup

    That's kind of you to say, and I hope you can make use of it.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,758

    Re: List All Dates Between Two Dates and Vlookup

    no helper columns required.
    ARRAY formula is used.
    In K5 then copy down
    Please Login or Register  to view this content.
    In L5 then copy down
    Please Login or Register  to view this content.
    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  11. #11
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Thanks Srinivasa, Is it possible to implement the formula in Column O of sheet April 19 I would like the Column O to populate the if someones on annual leave from the sheet Leave Record sheet and if the date falls under a bank holiday then the type of BH is returned from the UK Bank Hols sheet.

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,758

    Re: List All Dates Between Two Dates and Vlookup

    I guessed the expected results in April sheet as below.
    In O5 then copied down
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-12-2019 at 09:44 AM.

  13. #13
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Thank you so much for your time.

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,758

    Re: List All Dates Between Two Dates and Vlookup

    Sorry for the mistake in the formula. Corrected Formula in O5 is
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-11-2019
    Location
    Nottingham
    MS-Off Ver
    MS Office
    Posts
    42

    Re: List All Dates Between Two Dates and Vlookup

    Thanks Srinivasa

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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