+ Reply to Thread
Results 1 to 5 of 5

Excel 2013 - if date is found between date range return value

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 365
    Posts
    51

    Question Excel 2013 - if date is found between date range return value

    Hi there,
    I have searched high and lo on the net looking for some formula that will return the value if the date falls within a range.

    In this example below, I have a sheet with dates in column A (every date from 2010 to 2019) I want a formula that checks the date in Column A and compares it to the named range SchoolHolidays, start date range1 is in column J and the end date range is in column K and returning the content in Column L into Column F

    I have tried various variations of Vlookup, but that only finds the date if it is the actual date in column J and enters the Sch Hol in column F and doesn't find the next date. If the date is not within range, then just leave cell blank.

    I know it shouldn't be so difficult, but I haven't been able to find something to manipulate to get what I am after.

    In the example below I manually entered in the 3/4/2010 - just to show what it was supposed to do.

    Thank you for having a look at this for me.

    Cheers,
    TheShyButterfly

    Lookup date in date range and return content of adjacent cell.JPG

  2. #2
    Registered User
    Join Date
    12-06-2016
    Location
    India
    MS-Off Ver
    2007
    Posts
    72

    Re: Excel 2013 - if date is found between date range return value

    Hi,

    You can simply use IF statement here
    =IF(AND(A2>=H2,A2<=I2),K2,"")

    Hope this solved your prob

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    Australia
    MS-Off Ver
    Excel 365
    Posts
    51

    Re: Excel 2013 - if date is found between date range return value

    Thank you for your amazingly fast response

    But unfortunately the problem is not resolved.

    Even after changing the cell references (which should be looking between the date range in column J & K, and returning the value listed in column L).

    So, when entering in the formula =IF(AND($A2>=$J3,$A2<=$K3),$L3,"") into column F, it returns a blank cell.
    The formula doesn't appear to be checking the dates in Column A within the date ranges in columns J & K.

    I would have thought that you would have used the named range SchoolHolidays which incorporates columns J, K and L.

    Sorry, I also thought it would be a quick and easy fix.
    Perhaps I should format the date range columns differently ... but I thought that there would be some solution that would be able to handle the way it is at the moment.
    Cheers,
    TheShyButterfly

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2501
    Posts
    13,676

    Re: Excel 2013 - if date is found between date range return value

    Hi TheShyButterfly.

    Are you certain that the dates you are referencing are actual dates and not text that looks like "dates". If you are not aware of it dates are numbers that are formatted for human eyes.

    For example 2/04/2010 as text has a numeric value of zero just as a postal code or telephone number would. If that is an actual date it has an underlying numeric value ... 40213 in this case. That's the 40213rd day starting from 1/1/1900. The formatting is cosmetic. What Excel "sees" is 40213.

    To check those dates enter this formula in an empty area.

    =ISNUMBER(A3) and fill down. It returns TRUE for numbers and FALSE for text. Do the same with the cells in column J and K.

    Better yet upload a small Excel file example of what you are working with. Screen shots and pics can't tell enough of the story, and a file gives us something to work with in context. Be sure to desensitize the data.

    If you don't know how to upload an Excel file:

    To attach a file to your post,
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,168

    Re: Excel 2013 - if date is found between date range return value

    Try this ...

    =IFERROR(LOOKUP(2,1/($J$3:$J$1000<=$A2)/($K$3:$K$1000>=$A2),$L$3:$L$1000),"")

+ 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. Replies: 5
    Last Post: 09-25-2014, 10:42 AM
  2. 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
  3. Replies: 2
    Last Post: 06-07-2013, 02:21 PM
  4. Replies: 15
    Last Post: 04-08-2013, 12:40 PM
  5. [SOLVED] Create Fiscal Year - Lookup Date in Fiscal Month Date Range and Return EOM Date
    By gbriscoe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 03:29 PM
  6. Find a min value and return date where it was found
    By ABSTRAKTUS in forum Excel General
    Replies: 6
    Last Post: 05-22-2010, 10:14 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

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