+ Reply to Thread
Results 1 to 13 of 13

Return value if in date range

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Return value if in date range

    Newbie here, all all.

    I have a list of dates in a column i.e 03/06/2014 (A2)

    I then have a table which has a start date in the first column Z1, an end date in the second column Z2 and a year in the third column Z3.

    I want to look at the date in A2 and see where this falls within the 2 dates within Z1 and Z2. If it does return the value in Z3 into A3

    Note: that there are 5 date ranges and 5 years that they can fall against

    Your help would be appreciated

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,015

    Re: Return value if in date range

    Z1 is not a column it is a cell.
    Z2 and Z3 are not columns, they are cells.
    Z is a column
    A is a column as well.

    You need to define your problem in more detail, maybe post a sample file or create a mock up.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Re: Return value if in date range

    Sorry I didn't explain that at all well. Would have helped if id have been looking at my spreadsheet at the time.

    I wish to see if the date in A2, falls between the dates in table Z and AA, if so to return the value in AB into B2.

    So any date that falls between 30/09/13 and 31/10/14 would equal 2013, any date that falls between 30/09/14 and 31/10/15 equals 2014 etc

    Yeardate.jpg

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return value if in date range

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Re: Return value if in date range

    Hope this makes sense

    Yeardate2.jpg

    If the date falls between the date range in the table, it should return the year value from AB into B

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,015

    Re: Return value if in date range

    Assuming the date ranges cover a full year as your example suggests then
    in B2
    =VLOOKUP(Z2,A2:B20,2,1)
    and copy down the column

  7. #7
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Re: Return value if in date range

    Im getting an #N/A error. Im not sure that looks right to me.

    Just to be clear the dates in column B would usually be blank, I just populated these as an example. I want the formula to look at the date in A, work out if it falls between the date range of Z to AA and then provide the corresponding year from AB into B.

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,909

    Re: Return value if in date range

    Try this in cell AB2:

    =IFERROR(LOOKUP(2,1/($A$2:$A$1000>=Z2)/($A$2:$A$1000<=AA2),($B$2:$B$1000)),"")

    Copy down.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,125

    Re: Return value if in date range

    In B2

    =IFERROR(VLOOKUP($A2,$Z$2:$AB$5,3,1),"")

  10. #10
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Re: Return value if in date range

    Hi Phuocam, Im getting a 0 value

  11. #11
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    53

    Re: Return value if in date range

    Quote Originally Posted by JohnTopley View Post
    In B2

    =IFERROR(VLOOKUP($A2,$Z$2:$AB$5,3,1),"")

    You my friend are a star!! Thank you

  12. #12
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,015

    Re: Return value if in date range

    Quote Originally Posted by Special-K View Post
    Assuming the date ranges cover a full year as your example suggests then
    in B2
    =VLOOKUP(Z2,A2:B20,2,1)
    and copy down the column
    This should have been

    in B2
    =VLOOKUP(A2,Z$2:AB$5,3,1)

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,125

    Re: Return value if in date range

    Thank you for the feedback.

    If your question has been answered,please mark the thread as solved ("Thread Tools" at top of first post)

+ 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. Check a date range and return value based on the date range
    By KeithCar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-13-2015, 12:55 PM
  2. [SOLVED] reference date time range, return the value into all cell that match datetime range
    By Jarvco13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2013, 10:39 PM
  3. Search date range +/- 7 days and return date that falls within range
    By tlafferty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-13-2013, 03:26 PM
  4. Replies: 2
    Last Post: 06-07-2013, 02:21 PM
  5. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  6. [SOLVED] lookup date within date range grid to return fiscal month value
    By tigerseye001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2012, 02:27 PM
  7. Compare Dates To Date Range & Return Date Based On Outcome
    By gusmontoya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2008, 09:06 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