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,

Waldis

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):

=MID(A1,9,IF(LEN(A1)=27,2,IF(LEN(A1)=26,1,IF(LEN(A1)=25,1,0))))&"-"&MID(A1],5,3)&"-"&MID(A1],FIND("2016",A1),4)

Re: Date string conversion

Thanks, Skywriter!

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

=MID(TRIM(MID(A1,4,12)),5,2)&LEFT(TRIM(MID(A1,4,12)),3)&RIGHT(TRIM(MID(A1,4,12)),4)

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:

=DATEVALUE(MID(TRIM(MID(A1,4,12)),5,2)&LEFT(TRIM(MID(A1,4,12)),3)&RIGHT(TRIM(MID(A1,4,12)),4))

Thanks again, Skywriter!