Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Converting Imported Data to a Date Format

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am importing data from my AS400 in Excel, the dates are coming into the spreadsheet as numerics. How do I convert these numbers into a date format? i.e. 3202002 should be 3/20/2002, how do I get the number to that format??

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey there,

    There's likely a sleeker formula but heck try this:

    =(LEFT(A2,LEN(A2)-6)&"/"&LEFT(RIGHT(A2,6),2)&"/"&RIGHT(A2,4))+0

    You'll likely want to format the cell with a date format (otherwise you'll get the serial number behind the actual date).

    Hope that helps out,
    Adam

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Location
    Beirut
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default number 20050812 to date

    urgent please
    what can change if the text is th reverse as in the year, month then date

    how can I change the formula given above
    (LEFT(A2,LEN(A2)-6)&"/"&LEFT(RIGHT(A2,6),2)&"/"&RIGHT(A2,4))+0

    to get the date correct
    thanks

  4. #4
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi

    Try using:

    =TEXT(A1,"0000-00-00")+0
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Location
    Beirut
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanksa lot richard
    it worked fine

  6. #6
    New Member
    Join Date
    Mar 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Similar problem - but solution doesn't work

    I have a similar situation -- an inherited Excel spreadsheet with a column of "dates" in numeric format, that appear as yyyymmdd: e.g. 20040212

    But when I try the suggested code above:
    =TEXT(A1,"0000-00-00")+0

    I just get a #VALUE! error. When I take off the final "+0" I get the hyphens added in, but I still can't convert this to an Excel date. If I go into Excel and try to change the format (to Date MM-DD-YYYY), nothing happens. Even if I copy the cell and past the value into a new cell, this doesn't work either. Can anyone help? Thanks!!

  7. #7
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi Hilary

    Give the following a shot:

    =DATE(LEFT(A1&"",4)+0,mid(A1&"",5,2)+0,RIGHT(A1&"",2)+0)

    altho I am surprised the Text formula didn't work.
    Richard Schollar

    Using xl2013

  8. #8
    New Member
    Join Date
    Mar 2004
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Richard,

    This works perfectly! It's exactly the result that I wanted. Thank you so much for helping me with this.

  9. #9
    New Member
    Join Date
    Dec 2007
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default work when importing data in 10/29/07 - 11/4/07 format?

    Will this work for dates that are already formated and imported as:

    10/29/07 - 11/4/07 data1 data2 data3 data4
    11/5/07 - 11/11/07 data1 data2 data3 data4

    Currently when I use this date format and create a pivot table, the dates are never in order even after sorting them.

  10. #10
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi Armacc2 & Welcome to the Board!

    You effectively have two dates there - do you want to preserve hem both, or will just the first do? I would try the TextToColumns route, and specify the "-" (w/o quotes) as the delimiter and do a MDY date format import (I think this has already be mentioned towards the top of this thread) - you can select not to import the second date if required.
    Richard Schollar

    Using xl2013

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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