date and time conversion

[deb]

Excel 365

I have dates and time (text format) European style that I need to setup like American formatting as a date.

23.05.2012 14:11:56 changed to 05/23/2012 14:11:56
24.08.2011 17:24:39 changed to 08/24/2011 17:24:39
06.04.2012 12:18:58 changed to 04/06/2012 12:18:58
06.04.2012 11:59:27 changed to 04/06/2012 11:59:27
24.08.2011 16:03:28 changed to 08/24/2011 16:03:28


Thanks in advance,

 

[Joe4]

For a value in cell A1, use this formula:

=DATEVALUE(MID(A1,4,2) & "/" & LEFT(A1,2) & "/" &  MID(A1,7,4)) + TIMEVALUE(RIGHT(A1,8))

which will convert it to a valid date/time.

Then you can apply your Custom format of:
mm/dd/yyyy hh:mm:ss

 

[deb]

Thank you, this works great

 

[Joe4]

You are welcome.