Help with a Formula to Flip Date Format

[tbruce]

Hi all,

I am trying to come up with a formula to flip the date format in a column/cell specific to the following:

2017/09/07 01:19:52(GMT -4)

I need the date in the following mm/dd/yyyy so it would appear like this:

09/07/2017 01:19:52(GMT -4)

I have 2,000+ lines of data that need this conversion. Any help with a formula would be much appreciated.


Much thanks to the great minds out there.

 

[bstaaa]

Try splitting the data with the 'Text to Columns' feature under the Data tab. Then add the following formula the the right of the now 2 columns with your split data (adjusting the cell1 and cell2 mentions for the actual cell references):

=TEXT(cell1,"mm/dd/yyyy ") & cell2

Then you can drag the formula down. Hope this helps!

 

[jtakw]

Hi,

Another formula option without the extra column:

Book1
	A	B
1	2017/09/07 01:19:52(GMT -4)	09/07/2017 01:19:52(GMT -4)
Sheet1

Cell Formulas
Range	Formula
B1	=TEXT(LEFT(A1,FIND(" ",A1)),"mm/dd/yyyy ")&RIGHT(A1,LEN(A1)-FIND(" ",A1))

Formula copied down as needed.

 

[barry houdini]

This formula should do it.....

=REPLACE(MID(A1,6,99),6,0,"/"&LEFT(A1,4))