On Thu, 30 Oct 2008 10:52:23 -0700, BDT wrote:

Thanks everybody.

The formula:

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

worked fine when I deleted the 2 minus signs after the equal sign and the
other one:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),RIGHT(A1,2))

generated just the date which might be helpfull. But I couldn't get the
Custom format to work with yyyy/mm/dd hh:mm:ss or mm/dd/yyyy hh:mm:ss. These
just return an infinite row of # signs as wide as you set the column width.

thanks again, BDT

Your doing something unexpected, or your data is not as you describe.

The TEXT function, after you removed the double unary, will return a text
string and not a true date. So formatting will predictably not have any
affect.

The DATE function, generates both time and date -- you probably didn't format
the result it properly.

Getting an infinite row of # signs in response to your date/time formatting
will occur if there is a negative number in that cell. Possibly you only
removed one of the minus signs, or your value is somehow negative.

Post back with more details of the precise problem, as well as copies (using
the Windows copy/paste functions) of the information that is present in the
FORMULA BAR when you select the source cell and the formula cells.
--ron