Date formatting
Date formatting
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Date formatting

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

    Default

     
    I have read all what is in the "Help" directory. I have a col of dates given in "CCYYMMDD" format and I want to insert a "/" between the year month and day but am only getting a whole row of "#####"'s. How do I get the date of 20021116 to read 2002/11/16 and format it all the way down the column. Sounds simple enough but I just cannot get it to work.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works to view your date, but won't change it to a date field:

    0000"/"00"/"00
    ~Anne Troy

  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will give you the date in another column:

    =LEFT(A1,4)&"/"&MID(A1,5,2)&"/"&RIGHT(A1,2)

    You can then take those dates, copy, edit and paste special as values. Then do the *Times One Fix*. Put a 1 in any cell. Copy it. Select the *bad dates*, hit Edit-Paste Special-Multiply (returns date serial number). Then format the cells as dates however you like.
    ~Anne Troy

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-05 03:43, Bingo58 wrote:
    I have read all what is in the "Help" directory. I have a col of dates given in "CCYYMMDD" format and I want to insert a "/" between the year month and day but am only getting a whole row of "#####"'s. How do I get the date of 20021116 to read 2002/11/16 and format it all the way down the column. Sounds simple enough but I just cannot get it to work.
    1. After selecting your values choose the Data | Text to Columns... menu command
    2. Press [ Next ] to 'til you reach Step 3 of 3.
    3. Set the Column data format to Date: YMD and press [ Finish ].
    4. Format as yyyy/mm/dd

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
  •  

 

 
DMCA.com