Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reformat text data in excel (Win 2000, Excel 2002)

    Hi -
    I need to have data that comes in like this...
    5207
    021107
    51207

    ..into dates without manually having to add zeros and dashes.
    Any suggestions?

    Thanks,
    Kirsten

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reformat text data in excel (Win 2000, Excel 2002)

    Welcome to Woody's Lounge!

    Entering dates this way can easily cause problems - a value such as 5207 can be interpreted as 5/2/07, i.e. May 2, 2007, but also as 5/20/7, i.e. May 20, 2007.

    You could use the Worksheet_Change event to convert values to dates. See Date And Time Entry on Chip Pearson's website. It assumes that you're familiar with Excel VBA.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reformat text data in excel (Win 2000, Excel 2

    1] If you can amend 5207 into 50207

    2] Then, select you column datas >> Data >> Text to Columnů>> Step 1 , click next >> Step 2, click next >>

    Step 3, Column date format, choose : "Date", and select "MDY" from the dropdown list >> Finish

    Regards

Posting Permissions

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