+ Reply to Thread
Results 1 to 9 of 9

Vlookup Different date formats

  1. #1
    Registered User
    Join Date
    09-24-2020
    Location
    Oslo
    MS-Off Ver
    2016 Professional
    Posts
    4

    Post Vlookup Different date formats

    Hi,
    I have 2 different excel workbooks and trying to use Vlookup formula.I wish to extract the information when the dates match.However, I'm getting error (NA)-The reason I suspect is that the dates are in different format in both the sheets. I cannot change format in the first excel(attached-Excel1) because it comes from running a program. How can I change the format in (attached- Excel2)-to fix this error please? Ive used Text to Columns method but its not helping. Would request someone to kindly help me fix this!
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,286

    Re: Vlookup Different date formats

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    The dates in the first file are not true dates, they are just text values that contain numbers and decimal points (and look a bit like dates)
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,745

    Re: Vlookup Different date formats

    I used Get and Transform sometimes called Power Query which is found on the Data Tab of your Excel Version.
    Bring each of the files into the Power Query Editor.

    Transform the dates to be consistent. Delete unnecessary columns and rows. Join the two tables on a common field and close and load to a new spreadsheet.

    See the attached file for the actions and Mcode.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-24-2020
    Location
    Oslo
    MS-Off Ver
    2016 Professional
    Posts
    4

    Re: Vlookup Different date formats

    When using text function as in your formula- I'm getting error. It gives error Attachment 696767 unfortunately.So the entire formula doesn't work!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,286

    Re: Vlookup Different date formats

    You probably need to change the commas ( , ) to semi-colons ( ; ) for your Regional Settings.

  6. #6
    Registered User
    Join Date
    09-24-2020
    Location
    Oslo
    MS-Off Ver
    2016 Professional
    Posts
    4

    Re: Vlookup Different date formats

    Quote Originally Posted by TMS View Post
    You probably need to change the commas ( , ) to semi-colons ( ; ) for your Regional Settings.
    Tried that as well. But that's not the reason perhaps. Excel is identifying only first 5 characters. Len(A1) gives 5 and it picks 5 characters to dd.mm. You can also try the excel file...

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,286

    Re: Vlookup Different date formats

    I DID try the excel files. That's why I KNOW the formula works.

    Are the samples you posted based on the real files?

  8. #8
    Registered User
    Join Date
    09-24-2020
    Location
    Oslo
    MS-Off Ver
    2016 Professional
    Posts
    4
    Quote Originally Posted by TMS View Post
    I DID try the excel files. That's why I KNOW the formula works.

    Are the samples you posted based on the real files?
    Thanks for helping. Can you share the excel with the formula where it works please- That would really help ? The excel which I attached is a sample based on real files.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,286

    Re: Vlookup Different date formats

    A true date is actually a number, typically 5 digits long. Hence, =LEN(A1) = 5 BUT =A1 formatted as General = 44075.
    Attached Files Attached Files

+ 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. Vlookup won't recognize date formats from drop down lists
    By ckearley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2017, 09:38 PM
  2. Replies: 2
    Last Post: 01-31-2017, 10:00 PM
  3. Date Format Formulam - Mixture of date formats and seperators
    By kp1983 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2016, 02:12 PM
  4. VBA:comboboxes to present same date in different formats...ADAPT WITH CHANGE IN DATE
    By AlexDobbin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2015, 08:30 PM
  5. VLookup and date formats
    By dalecooper in forum Excel General
    Replies: 6
    Last Post: 05-23-2011, 02:42 PM
  6. [SOLVED] vlookup with date formats
    By Enron in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2006, 11:30 AM
  7. [SOLVED] vlookup formats
    By CJRolls in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2006, 11:35 AM

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