If 5/4/2007 7:23:52 does not display as 2007-05-04 7:23AM
that suggests that the source cell may have leading spaces
and is TEXT, not an actual DATE.

To test....try this
A1: (the date to be converted)
B1: =TEXT(TRIM(A1),"yyyy-mm-dd h:mmam/pm")

Does that display properly?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Lumi" wrote in message
...
I tried but the date does not change I still have 5/4/2007 7:23:52
rather than 2007-12-30. I tried the custom format and other formats but
nothing changes stings but it seems that no changes take effect. When I
enter
the function you gave it displays the same information. Any hints!

"Ron Coderre" wrote:

If you only need to format the existing dates,
Try this Custom Number format:

Select the range of dates
From the Excel Main Menu:
<format<cells<number tab
Category: Custom
Type: yyyy-mm-dd h:mmam/pm
Click [OK]

If you want to use a formula in another column
then...with a date in A1
This formula displays that date in the format you requested:
B1: =TEXT(A1,"yyyy-mm-dd h:mmam/pm")

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Lumi" wrote in message
...
I have list with dates eg 11/24/2007 9:45 pm I need to transform those
dates
to 2007-11-24 9:45pm. I have tried the cell format and the data,text to
colum
ymd and still have not been able to fix it. I so some one had add extra
column and ad a function. I just don't know how to make the function
convertion. I realy apreciate any input with this issue.