+ Reply to Thread
Results 1 to 4 of 4

Excel not reading in dates correctly

  1. #1
    Registered User
    Join Date
    07-07-2014
    Location
    philadelphia
    MS-Off Ver
    2013
    Posts
    2

    Excel not reading in dates correctly

    I am trying to copy and paste dates from a PDF in the format, Jul-94 which represents July, 1994 (doesn't matter which day). I am using the text to columns tool to parse the data, and selected MYD for the date column, but it still isn't reading in the dates correctly. It handles all dates before 2001 ok; it converts Jul-94 to 7/01/1994. However for a date such as Dec-13, it is converting it to 12/13/2014 when it should be 12/01/2013. Is there any way to correct this problem?

    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,086

    Re: Excel not reading in dates correctly

    This is Excel trying to be helpful. Effectively, you have offered two parts that look like parts of a date. Unfortunately, Excel will take this as a month and a day and default the year to the current year ... whereas, you would like it to interpret it as the month and year and magic up the day. It isn't going to do that.

    Regards, TMS
    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
    07-07-2014
    Location
    philadelphia
    MS-Off Ver
    2013
    Posts
    2

    Re: Excel not reading in dates correctly

    However it does magic up the day for dates such as Jul-94. It assumes July, 01, 1994. However this only works for numbers after the month that are not possible as days of the month. That's why Jul-00 also works. However anything between 1-31 such as Jul-14 will return 7/14/2014. In this case, excel decides to magic up the year as this year. However, I have resolves this through a work around by parsing the original date into two cells, Jul and 01. Then I add 2000 to the 01 part and concatenate it back together. Excel....

  4. #4
    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,086

    Re: Excel not reading in dates correctly

    No, it sees the year when it's in the distant past ... that is, it's not going to find a day or month in 20's, 30's, 40's, 50's, 60's, 70's, 80's, or 90's

    One of Excel's foibles

    Glad you've found a workaround.

    Regards, TMS

+ 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. [SOLVED] Dates in Excel Spreadsheet won't sort correctly.
    By rbdiesel in forum Excel General
    Replies: 9
    Last Post: 05-06-2014, 02:06 PM
  2. [SOLVED] Calculating difference between 2 dates in excel 2003 - formula not working correctly
    By cf7046 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 10:18 PM
  3. Excel 2007 Not Displaying Dates Correctly
    By Gary1981 in forum Excel General
    Replies: 3
    Last Post: 01-29-2013, 05:13 AM
  4. Dates not reading correctly
    By TIERNAN in forum Excel General
    Replies: 6
    Last Post: 11-04-2012, 09:32 AM
  5. [SOLVED] Formula Not Reading Cell Data Correctly
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-08-2012, 04:19 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