Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting Text to Date (Excel 2002)

    I'm trying to convert the text 100506 into a date. What I want is 5-Oct-06. What I get is 5-Oct-28. Any suggestions are appreciated.

    Thanks, SStewart

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

    Re: Converting Text to Date (Excel 2002)

    With the text value in A1, try the formula
    <code>
    =DATE(RIGHT(A1)+2000,LEFT(A1,2),MID(A1,3,2))
    </code>
    or
    <code>
    =DATE(MOD(A1,100)+2000,INT(A1/10000),TRUNC(MOD(A1/100,100)))</code>

  3. #3
    New Lounger
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Text to Date (Excel 2002)

    Hello Hans,

    I tried both of these formulas and I get 1/0/1900 as a result.

    Thanks, SStewart

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

    Re: Converting Text to Date (Excel 2002)

    Check the cell with the text. Does it contain leading or trailing spaces?

  5. #5
    New Lounger
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Text to Date (Excel 2002)

    no leading or trailing spaces but it originally was formatted as General if that makes a difference.

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

    Re: Converting Text to Date (Excel 2002)

    Could you attach a small sample workbook?

  7. #7
    New Lounger
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Text to Date (Excel 2002)

    Here's a sample. Column A and B need to be converted to dates.

    Thanks, SStewart

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

    Re: Converting Text to Date (Excel 2002)

    The 2nd formula I proposed works correctly for column A (the 1st one fails because the values are numbers, not text).

    Cell B2 contains 2008326. What date is that supposed to be?

  9. #9
    New Lounger
    Join Date
    Apr 2006
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Text to Date (Excel 2002)

    Column B has the year, month, date.

    It's funny, we downloaded this information from a database. It came in as different formats.

    Thanks, SStewart

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

    Re: Converting Text to Date (Excel 2002)

    No, Column B is not in the format year, month, date. It suddenly dawned on me that it contains exactly the same dates as column A, but in the format year (4 digits) followed by the day of the year (3 digits), e.g. January 1, 2006 is 2006001 (the 1st day of 2006), and December 31, 2006 is 2006365 (the 365th day of 2006).
    See attached version, with formulas in columns D and E.

Posting Permissions

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