Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text to Dates (2003)

    I am trying to convert text data which is not in an Excel date format to a date format.

    The two forms the text takes is ddmmyyyy or dmmyyyy (e.g. 08032005 or 8032005) i.e. with no /'s so as far as I can tell Excel won't recognise the entries as dates. The list I am trying to convert contains both forms and I would like a formula or function that could deal with both.

    Is there an easy way to do this - so far I have tried concatentating and including /'s but I can't work out how to do this for the second case.

    Thanks!

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Text to Dates (2003)

    Ok
    try =LEFT(A1,(LEN(A1)-6))&"/"&MID(A1,(LEN(A1)-5),2)&"/"&RIGHT(A1,4) (where A1 contains the data)

    This should handle both forms
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Dates (2003)

    The following would work for both formats.

    =DATE(RIGHT(A1,4),LEFT(TEXT(A1,"00000000"),2),MID( TEXT(A1,"00000000"),3,2))

  4. #4
    New Lounger
    Join Date
    Mar 2006
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text to Dates (2003)

    Thanks!

Posting Permissions

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