convert date format

[derekopswat]

I need to convert a column in the format:
Jan 02 2014 08:08:00 PST

to the format:
02 January 2014
or if another date format is easier, that's ok, too.


Is there a formula that I could enter into a helper column to make this work?

An excerpt from the file is attached.
date-adjustment-example.xlsx

[Pete_UK]

You can use this formula to convert the date that is in A2 (put the formula in Q2):

=DATE(MID(A2,8,4),MATCH(LEFT(A2,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID(A2,5,2))

and then apply a custom format to that cell in the style you would prefer (i.e. dd mmmm yyyy). Then you can copy the formula down.

Hope this helps.

Pete

[Haseeb Avarakkan]

Or,

=SUBSTITUTE(LEFT(TRIM(A2),11)," ",", ",2)+0

Then change the format.

[Pete_UK]

Yeah, that's what I tried to do, but it doesn't work in the UK because we have a different date format where the month is expected after the day.

Pete

[derekopswat]

Thanks! Both of those formulas worked perfectly.

[Haseeb Avarakkan]

Yes Pete, this will give error on d/m/y format.

May be we can shorten MONTH part ie, MONTH(1&LEFT(A2,3))

=DATE(MID(A2,8,4),MONTH(1&LEFT(A2,3)),MID(A2,5,2))

[Pete_UK]

Yeah, good idea.

It's good to see you posting again - Happy New Year.

Pete

[Haseeb Avarakkan]

Happy New Year to you Pete