re-arrange a cell that contains date/time

[STEMALO]

Hi everyone,

current cell is:
5/6/2014 7:57:24 PM

would like it to be:
2014/06/05 19:57:24

is this possible?

 

[jtakw]

Hi,

Assuming 5/6/2014 is May 6th, 2014

Just reformat the cell as yyyy/dd/mm hh:mm:ss

 

[STEMALO]

Nope, it stays the same

 

[jtakw]

Then the value in your subject cell is TEXT, not real Date/Time values.

Assuming data in A1, in an unused cell, put =ISNUMBER(A1)
What do you get?

 

[STEMALO]

false

the cell is currently formatted as GENERAL.
Tried to reformat it but doesn't work.
Even tried splitting it..

 

[jtakw]

Again, assuming your sample is May 6th, and not knowing what Other dates you may have, this should do the job:

Book1
	A	B	C
1	5/6/2014 7:57:24 PM		2014/05/06 19:57:24
2			Custom Format C1
3			yyyy/dd/mm hh:mm:ss
Sheet290

Cell Formulas
Range	Formula
C1	=(MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",2))+1,4)&"/"&SUBSTITUTE(MID(A1,3,2),"/","")&"/"&SUBSTITUTE(LEFT(A1,2),"/","")&MID(A1,FIND(" ",A1),15))+0

 

[jtakw]

Oops, I had the day and month backwards, corrected formula below:

Book1
	A	B	C
1	5/6/2014 7:57:24 PM		2014/06/05 19:57:24
2			Custom Format C1
3			yyyy/dd/mm hh:mm:ss
Sheet290

Cell Formulas
Range	Formula
C1	=(MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",2))+1,4)&"/"&SUBSTITUTE(LEFT(A1,2),"/","")&"/"&SUBSTITUTE(MID(A1,3,2),"/","")&MID(A1,FIND(" ",A1),15))+0

 

[STEMALO]

i can't thank you enough...it works perfectly..
Now going to open a beer and reverse engineer your formula.

Again thanks

 

[jtakw]

You're welcome, glad it worked for you.

Cheers

 

[jtakw]

I must apologize STEMALO, there are times when I overthink the problem and solution, and not have had a good night sleep last night and my mind being a little "cloudy", this is definitely one of those times.

The solution can simply be:

Book1
	A	B	C
1	5/6/2014 7:57:24 PM		2014/06/05 19:57:24
2			Custom Format C1
3			yyyy/dd/mm hh:mm:ss
Sheet290

Cell Formulas
Range	Formula
C1	=A1+0