date format issue

[reellavkastning]

I have this format in one cell stored as text

1/1/2008 12:07:26 PM

The problem is to convert it to something like:

01.01.2008

I need to get rid of the timestamp plus the leading zeros problem with the date format.

 

[Andrew Poulsom]

If the data and time is in A1 use:

=INT(A1)

and format the cell as dd.mm.yyyy.

 

[reellavkastning]

That would have worked but it is

1/13/2008 12:18:00 PM

mm/dd/yyyy format.

That returns the #VALUE! if I use the INT and format method.

 

[Andrew Poulsom]

Is it a text entry? What dows ISNUMBER return when applied to that cell?

 

[reellavkastning]

It returns the value false.

I have to import it as text, otherwise excel recognizes some of the data as dates but others as text, so I get some 1/1/2008 and some 01.13.2008
...

 

[reellavkastning]

01.12.08 12:05 12.1.2008
01.12.08 13:38 12.1.2008
01.12.08 13:46 12.1.2008
01.12.08 15:11 12.1.2008
1/13/2008 12:18:00 pm #value!

 

[Andrew Poulsom]

Try:

=TEXT(MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",2))+1,4)&TEXT(SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,"/","|",2))-1),"/",""),"0000"),"0000\/00\/00")+0

 

[reellavkastning]

wow!

It works! Thanks a lot.