Date string conversion

  • Hi,

    I'm trying to convert a string that looks like this: "Wed Aug 31 2016 11:30pm EDT" to a date "42613" (or "08/31/2016" when formatted as date)

    It looks simple at the first look, but I'm stuck after trying to extract, concatenate, etc.

    I would be grateful if someone could help me with a formula that achieves this result.

    Thank you,


  • Re: Date string conversion

    The string's length changes based on day and time, another example would be "Fri Jul 1 2016 2:30pm EDT)

  • Re: Date string conversion

    The closest formula I've come up with is (if A1 is the cell with the string):


  • Re: Date string conversion

    Thanks, Skywriter!

    It was pulling the day in the middle, but I changed it a little, like this:


    It's almost there, the only problem - it's placing a space after a single digit day of the month.

  • Re: Date string conversion

    Well, DATEVALUE() ignores the space and converts it to the right date value.

    This does the trick:


    Thanks again, Skywriter!

  • Re: Date string conversion

    I guess I left a cell I was referencing during the testing A3 in the formula.

    I'm glad you got it working. :cheers:

    Bruce :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!