+ Reply to Thread
Results 1 to 7 of 7

VLookup and date formats

  1. #1
    Registered User
    Join Date
    05-12-2011
    Location
    Notts, England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    10

    VLookup and date formats

    Hi everyone
    I have just decided to sign up for this forum as I'm constantly bashing my head against the table trying to work things out. Sometimes it's a good way to learn but on other occasions I'm stumped!


    So, sorry if my questions are dumb.. any help is much appreciated though so thanks in advance.

    I'm using a vlookup between worksheets. I have reference numbers on both sheets in Column A.. so finding a match is simple. Now, I have set my formula to return the corresponding date from the 2nd worksheet. My problem lies in the format that the dates return in. For example:

    21/01/2011 comes back to my sheet as 40564

    Now, when I go to format it to a date, the cell displays 22/01/2015

    I'm probably being stupid, but it's getting frustrating! Sorry if I forgot to include any vital info in this post. Thanks!

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

    Re: VLookup and date formats

    The vital thing(s) are: the formula you are using, the contents of the cells and, probably most vital, a sample workbook exhibiting the problem ;-)

    40564 does display as 21/01/2011 for me when formatted as a date, so I'm not entirely sure what it is you are doing.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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
    Registered User
    Join Date
    05-12-2011
    Location
    Notts, England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    10

    Re: VLookup and date formats

    I have no idea. I was using 2008 until this morning.. that worked fine. I upgraded to 2011 just now and all of a sudden it's displaying dates like that!

  4. #4
    Registered User
    Join Date
    05-12-2011
    Location
    Notts, England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    10

    Re: VLookup and date formats

    It must have something to do with my current worksheet anyway as when I format that figure in another one it works just fine.

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

    Re: VLookup and date formats

    How do you want to pursue this? I have Excel 2003 and Excel 2007, nothing higher.

    Regards

  6. #6
    Registered User
    Join Date
    05-12-2011
    Location
    Notts, England
    MS-Off Ver
    Excel 2011 Mac
    Posts
    10

    Re: VLookup and date formats

    Hi
    Thanks a lot for your help. I have tried to replicate the problem since and don't seem to be able too.. so at the moment I'm happy to put it down to one of those strange mysteries. I'll repost if it happens again though. Thanks again

  7. #7
    Registered User
    Join Date
    05-23-2011
    Location
    Mexico
    MS-Off Ver
    Excel 2011
    Posts
    1

    Re: VLookup and date formats

    Quote Originally Posted by dalecooper View Post
    Hi everyone
    I have just decided to sign up for this forum as I'm constantly bashing my head against the table trying to work things out. Sometimes it's a good way to learn but on other occasions I'm stumped!


    So, sorry if my questions are dumb.. any help is much appreciated though so thanks in advance.

    I'm using a vlookup between worksheets. I have reference numbers on both sheets in Column A.. so finding a match is simple. Now, I have set my formula to return the corresponding date from the 2nd worksheet. My problem lies in the format that the dates return in. For example:

    21/01/2011 comes back to my sheet as 40564

    Now, when I go to format it to a date, the cell displays 22/01/2015

    I'm probably being stupid, but it's getting frustrating! Sorry if I forgot to include any vital info in this post. Thanks!
    Hi, please check Format Cell, some times when you copy from other place and paste, maybe that cell has another format, could be a date data trying to paste in a Number format cell, please check it out! and tellme how was it.

    Regards!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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