+ Reply to Thread
Results 1 to 6 of 6

Vlookup on Dates and generating date range based upon inputs

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010
    Posts
    19

    Vlookup on Dates and generating date range based upon inputs

    Hello All,


    I am attempting to find a way to search through a database I created based upon every day of the year. My goal is to have a user input a beginning date and end date for a trip, then my functions will return the range specified with each day and it's individual temperature average (in my database).

    -Thanks to Al I have this problem solved.

    I also need help generating the dates between the start and end dates that are entered by the user. I was considering combining Days360 with an Hlookup that searches through a massive sheet with all the dates for the upcoming year and then going down 1 in the column for the difference in the dates in each column.

    Thank you for any advice or help!

    Vlookup Help.xlsx
    Last edited by Chardo; 02-14-2014 at 05:21 PM. Reason: Solved issue thanks to Al

  2. #2
    Forum Contributor
    Join Date
    01-22-2014
    Location
    genoa, italy
    MS-Off Ver
    Excel 2007
    Posts
    134

    Re: Vlookup on Dates Help - Removing Year from Date entered into Input

    hi,
    date input (1/2/2014) in cell A2.

    =VLOOKUP ( CONCATENATE(LEFT(TEXT(A2,"mm/dd/yyyy"),2),MID(TEXT(A2,"mm/dd/yyyy"),SEARCH("/",TEXT(A2,"mm/dd/yyyy"),1)+1,2)) ......rest of the vlookup formula.......

    AL

  3. #3
    Forum Contributor
    Join Date
    01-22-2014
    Location
    genoa, italy
    MS-Off Ver
    Excel 2007
    Posts
    134

    Re: Vlookup on Dates Help - Removing Year from Date entered into Input

    and you can replace SEARCH("/",TEXT(A2,"mm/dd/yyyy"),1)+1 by 4 .
    the formula as I sent it before was more complicated than necessary.
    sorry.
    AL

  4. #4
    Registered User
    Join Date
    02-13-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Vlookup on Dates Help - Removing Year from Date entered into Input

    Quote Originally Posted by AL1976 View Post
    hi,
    date input (1/2/2014) in cell A2.

    =VLOOKUP ( CONCATENATE(LEFT(TEXT(A2,"mm/dd/yyyy"),2),MID(TEXT(A2,"mm/dd/yyyy"),SEARCH("/",TEXT(A2,"mm/dd/yyyy"),1)+1,2)) ......rest of the vlookup formula.......

    AL
    Works beautifully! I will need to review the CONCATENATE formula because I have never used that before. I was trying combos of Left, Mid, and Right while using the Text formula and failing haha.



    Do you know about the automatic generation of the dates within the range if specified? I'm going to upload the actual file I'm using. Currently I was considering using Days360 to generate the number of days and then find some way to combine that with a giant date spreadsheet using an Hlookup. Not the prettiest way to get it done but all I could think of at the moment.

  5. #5
    Forum Contributor
    Join Date
    01-22-2014
    Location
    genoa, italy
    MS-Off Ver
    Excel 2007
    Posts
    134

    Re: Vlookup on Dates and generating date range based upon inputs

    hi,
    for the automatic generation of dates, does the attached file help ?
    AL

    date generation.xlsx

  6. #6
    Registered User
    Join Date
    02-13-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Vlookup on Dates and generating date range based upon inputs

    Quote Originally Posted by AL1976 View Post
    hi,
    for the automatic generation of dates, does the attached file help ?
    AL

    Attachment 297364
    I actually got a little help and then edited it myself! I realize that isn't big for most people here but as I'm learning I find it really interesting that I'm actually understanding what is going on. Hopefully I can set out to accomplish something in VBA soon by myself without any help.

+ 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. Input a year to determine the start date for 4-4-5 Calendar Year.
    By mak1176 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-25-2013, 10:34 AM
  2. Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  3. Removing everything but year from a date
    By joshnathan in forum Excel General
    Replies: 10
    Last Post: 07-16-2012, 09:57 AM
  4. Replies: 2
    Last Post: 08-09-2011, 09:54 AM
  5. removing year from date
    By Joe Man in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2005, 03:05 PM

Tags for this Thread

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