This date 20071017072917 into this type of date 10/17/2007 29:17

[Guest]

How to convert This date 20071017072917 into this type of date 10/17/2007 29:17

 

[JE McGimpsey]

One way (assuming you really wanted 10/17/2007 07:29:17 rather than what
you posted):

=--TEXT(A1,"0000\/00\/00 00\:00\:00")

Format as mm/dd/yyyy hh:mm:ss

t

 

[Ron Rosenfeld]

How to convert This date 20071017072917 into this type of date 10/17/2007 29:17

The formula:

=TEXT(A1,"0000\/00\/00 00\:00\:00")

will convert it to a date representation Excel can understand, but it will be
different than what you request:

2007/10/17 07:29:17


Also, this is a text string.

To convert this to a Excel date/time value, and then display it without showing
the hours, you could:

C1: =--B1 (assumes above formula is in B1)
or
B1: =--TEXT(A1,"0000\/00\/00 00\:00\:00")
Format/Cells/Number/Custom Type: mm/dd/yyyy mm:ss

OR you could use the formula:

=TEXT(TEXT(A1,"0000\/00\/00 00\:00\:00"),"mm/dd/yy mm:ss")

If you really want the hours to be displayed, and your request excluding them
was a typo, merely add hh: prior to the mm:ss

=TEXT(TEXT(A1,"0000\/00\/00 00\:00\:00"),"mm/dd/yy hh:mm:ss")


--ron

 

[Peo Sjoblom]

Shouldn't it be 10/17/2007 07:29:17?

If so you can use


=TEXT(LEFT(A1,8),"0000-00-00")+TEXT(RIGHT(A1,6),"00\:00\:00")

that will give you 39372.3120023148


which is the serial date and the decimal time value so then you need to
format the result as

mm/dd/yyyy hh:mm:ss which will return 10/17/2007 07:29:17



--


Regards,


Peo Sjoblom