Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Convert MMDDYYYY String to MM/DD/YYYY

This is a discussion on Convert MMDDYYYY String to MM/DD/YYYY within the Excel Questions forums, part of the Question Forums category; I'm using Excel 2000 and need to convert a column of dates in an Excel Spreadsheet into a csv file ...

  1. #1
    New Member
    Join Date
    Jun 2003
    Posts
    8

    Default Convert MMDDYYYY String to MM/DD/YYYY

    I'm using Excel 2000 and need to convert a column of dates in an Excel Spreadsheet into a csv file and then import it into another program. When I attempt to format the cells to the needed format it changes the dates. For example, the date 00/00/1914 when formated to date format changes to 2/23/4647. Any suggestions on how to change to the needed format?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,949

    Default

    Hi, welcome to the board.

    Have you tried using Data, Text to Columns ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Jun 2003
    Posts
    8

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    I am new to Excel, have tried Text to columns but had no luck. Can you be more specific?

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,487

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    What is the date 00/00/1914 supposed to represent?

  5. #5
    New Member
    Join Date
    Jun 2003
    Posts
    8

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    Some of the dates have only the year, some have only a month and a year. I had to convert them all to MMDDYYYY format by adding leading zeroes. 00/00/1914 means an unknown month and day in the year 1914

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    26,735

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    Since your dates aren't complete, you can't format them as dates. I think your best bet may be two convert them to text, since you want to export them to a CSV file.

    For example, if in cell A1 you have 1914, then in cell B1, you can use the formula:

    ="00/00/" & A1

    You will need to write a similar formula for the ones that have months (you didn't say how those were laid out).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,487

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    Would it be better to add 0101 or 01 instead of leading zeroes? Note that the entries in column A are Text:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    191401/01/1914**
    2
    01191401/01/1914**
    3
    0101191401/01/1914**
    4
    200301/01/2003**
    5
    02200301/02/2003**
    6
    0506200305/06/2003**
    7
    ****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in B1 is:

    =--TEXT(REPT("01",(8-LEN(A1))/2)&A1,"00-00-0000")

    formatted as dd/mm/yyyy.

  8. #8
    New Member
    Join Date
    Jun 2003
    Posts
    8

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    I'll give this a try. Any idea why my dates changed when I tried to format cells to date format? Is there a way to know if the Excel "dates" are in text or serial dates?

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,487

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    For me 1914 formatted as mm/dd/yyyy appeared as 03/28/1905, which is what I would expect. I don't know how you got 2/23/4647.

  10. #10
    New Member
    Join Date
    Jun 2003
    Posts
    8

    Default Re: Convert MMDDYYYY String to MM/DD/YYYY

    You are correct. I was looking at the wrong spreadsheet. What does that tell me about my data? Is it text, serial dates or what?

Page 1 of 2 12 LastLast

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
  •  


DMCA.com