Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Angry Unanswered: Why does Excel suck at matching dates? (VLOOKUP & INDEX/MATCH fury)

    I am trying to look up revenue for a given day. The date and revenue is coming from an embedded SQL data connection. When the query is refreshed, the Date is formatted as YYYY-MM-DD regardless of that columns format that i have set. The date being used as the lookup value is in the format DD/MM/YY (this format cannot be changed). So when i try an index/match or a vlookup, it is unable to match lookup value 12/25/15 to data connection value 2015-12-25, and I get the dreaded #N/A error. If I double-click on the 2015-12-25 cell from the data connection, and then hit enter, it changes to the format I set: 12/25/15, and then the lookup formula works. But as soon as teh data connection is refreshed, it reverts to the 2015-12-25 format and the lookup breaks again.

    Any idea on how to get around date formats when trying to use vlookup or index/match??

    Click image for larger version. 

Name:	tJIfsXp.png 
Views:	11 
Size:	13.5 KB 
ID:	16844

    Click image for larger version. 

Name:	e2.PNG 
Views:	6 
Size:	13.2 KB 
ID:	16845

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Coerce the values to be of type date within the vlookup https://www.google.co.uk/search?q=ms...ms+excel+cdate
    If that diexnt work then use force hidden columns to be dates. You may need to use other date function https://www.google.co.uk/search?q=ms...date+functions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Yea, i ended up forcing the lookup column to the same format using TEXT(lookupValue,"yyyy-mm-dd")

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So its not excel sucking when doing vlookup, but instead your vba skills that leave a bit to be desired

    Spreadsheets can be a problem as each cell contains a value and formatting. But you need to compare like with like. Personally i think youd be better of comparing dates with dates as opposed to text that looks like a date.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    So its not excel sucking when doing vlookup, but instead your vba skills that leave a bit to be desired

    Spreadsheets can be a problem as each cell contains a value and formatting. But you need to compare like with like. Personally i think youd be better of comparing dates with dates as opposed to text that looks like a date.
    I'm not using VBA, just a SQL query in a data connection. I agree thhat comparing dates to dates is best, which is what I was trying to do originally, but I cant get it to work. See my original post and images.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are using a function such as text then you are using vba.
    No you werent comparing dates with dates, you were comparing s date value with a text value, hence the problem
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by healdem View Post
    If you are using a function such as text then you are using vba.
    No you werent comparing dates with dates, you were comparing s date value with a text value, hence the problem
    The data type of the Date field coming from SQL server is DATE. The lookup value in excel is also set as Date. But for whatever reason that i have yet to figure out, they look different and don't match. What am I missing here? How do I ensure that the SQL query spits out the date in a way that is equal to the lookup value's date format?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look again at your spreadsheet, one or other of the values is text

    I am trying to look up revenue for a given day. The date and revenue is coming from an embedded SQL data connection. When the query is refreshed, the Date is formatted as YYYY-MM-DD regardless of that columns format that i have set. The date being used as the lookup value is in the format DD/MM/YY (this format cannot be changed)
    note your date format in D2 is MM/dd/yy, not dd/mm/yy

    if you have control of the query then expressly format it using whatever formatter in the SQL engine you are suing

    or import the data is is, then in an adjacent column reformat it in the style you want
    or you expressly coerce both values to be date within the vlookup

    if both are dates then TBH I don't know if its the different date separator that causes problems
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2016
    Location
    Minneapolis
    Posts
    4

    Matching Dates

    Excel will read and match the date as text, after that your result needs to be formatted as date.
    Last edited by HotBreakfast; 10-07-16 at 01:01. Reason: Typo

Posting Permissions

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