Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Text to Date Value

    I receive a date as the following text, Feb 14 2014, 04:00 PM EST and would like to use a formula to convert it to a date value. I have played around with (left(cell)11) and that will trim it to Feb 14 2014 but when I then use value on the string it returns an error #value.

    Any help is appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If in A1, you can use:
    =REPLACE(LEFT(A1,11),7,0,",")+MID(A1,14,8)

    [Note:Your extraction to "Feb 14 2014" will not be read by date value unless you add the comma to make it "Feb 14, 2014"...]

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I think the DATEVALUE function requires a comma before the year. If your text dates are MMM DD YYYY, then I believe this will work:

    =DATEVALUE(LEFT(A1,6)&", "&MID(A1,8,4))

    There must be a more elegant way, but this was my first thought.

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks 5 Star, you are the man. Worked fine and it now saves me one step.

    thanks Again

Posting Permissions

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