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

Thread: date formats

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

    Default

    I am importing data into excel from a Computer maintenance management system. The problem is the data that is imported is in a format excel doesn't recongnize. This is the result of a "year 2000 bug fix". The dates are in this format:

    991023 : which is Oct. 23, 1999
    1001023: which is Oct. 23, 2000
    1011023: which is Oct. 23, 2001
    1020403: which is Apr. 3, 2002
    How can I make excel put this into a standard format such as 10/23/99, or Oct/23/01. Please help.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-02 21:42, Lenny wrote:
    I am importing data into excel from a Computer maintenance management system. The problem is the data that is imported is in a format excel doesn't recongnize. This is the result of a "year 2000 bug fix". The dates are in this format:

    991023 : which is Oct. 23, 1999
    1001023: which is Oct. 23, 2000
    1011023: which is Oct. 23, 2001
    1020403: which is Apr. 3, 2002
    How can I make excel put this into a standard format such as 10/23/99, or Oct/23/01. Please help.
    Hi,

    Data in column A
    Cell A1 being tested here.

    =DATE(LEFT(A1,LEN(A1)-4)+1900,MID(A1,LEN(A1)-3,2),RIGHT(A1,2))

    and copy down the your list.

    This should work for you.

    Regards,
    Jay


  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Lenny


    How about

    =DATEVALUE(MID(A2,3,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,2))

    And format the cell as the date format needed.



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

    Default

    Works great Jay. Thank you very much.

    Regards, Lenny

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
  •