How to convert DD.MM.YYYY HR:MI:SE to MM/DD/YYYY

[bpflix]

Hello,

I am using excel 2010. I have dates in this format ( 29.01.2014 20:54:51) I need to convert to MM/DD/YYYY. How i do it.

BPFLIX

 

[Jonmo1]

Welcome to the board.

Assuming the date will ALWAYS be in that format, as 2digitday.2digitmonth.4digit year
Try
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

Format the cell with the formula as mm/dd/yyyy

 

[njimack]

=TEXT(SUBSTITUTE(A1,".","/"),"mm/dd/yyyy")

 

[Rick Rothstein]

=TEXT(SUBSTITUTE(A1,".","/"),"mm/dd/yyyy")

That may not work given the month-day order is reversed in the original text... I think I would go with Jonmo1's formula for this question (subject to the day number always being shown as two digits, which is fixable if not)

 

[bpflix]

Jonmo1's formula worked. Thanks all.

 

[Jonmo1]

Glad to help, thanks for the feedback.