Date change formula

[southrndawg15]

Who can help me?
I need a formula to change this date
2016-12-31 17:59:10
to display as
12/31/2016 17:59:10

 

[snjpverma]

Select the range in which you want the changes. Then click Ctrl+1 . go to Number >> Custom>>> and type mm-dd-yyyy hh:mm:ss

 

[southrndawg15]

The source this is imported from makes it weird. when I do what you said, it doesn't change the format. I need to insert a formula in a neighboring cell to change it.

 

[FormR]

I need to insert a formula in a neighboring cell to change it.

Hi, you could give this a try:

Excel 2013
	A	B
1	2016-12-31 17:59:10	12/31/2016 17:59
Sheet1

Cell Formulas
Range	Formula
B1	=A1+0

 

[southrndawg15]

I was using this formula for a similar cell without the time stamp
=MID(a2,7,2)&"/"&RIGHT(a2,2)&"/"&LEFT(a2,5)

But I need the timestamp at the end.

 

[63falcondude]

It looks like there is a non-breaking space at the end of the time stamp. I believe that is what's causing the issue. You can use a formula to remove it, then copy your column (with the formula in it) and paste as values.

Formula to remove trailing non-breaking space:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

 

[FormR]

But I need the timestamp at the end.

Hi, did you try the simple coercion formula in post 4? What was the result?

 

[southrndawg15]

#value!

 

[FormR]

#value!

It looks like there is a non-breaking space at the end of the time stamp.

If that's the case then an alternative:

Excel 2013
	A	B
1	2016-12-31 17:59:10	12/31/2016 17:59
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,19)+0

 

[Declamatory]

You could try =TEXT(A1,"mm/dd/yyyy hh:mm:ss")