 # 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

This seems to work with your two examples. :cool:

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

Bruce :cool:

• Re: Date string conversion

Thanks, Skywriter!

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

Code
``=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:

Code
``=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!

• 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!