Hmmm.....I hate to ask...but....you ARE using Microsoft Excel, right?
Let's keep checking.
Try this formula (assuming the source "date" is in cell A1):
=ISNUMBER(A1)
It should return: TRUE.
If it doesn't, there's a problem with the source value
and you'll need to post the exact contents of that cell
so we can hopefully see what's wrong.
Alternatively, you might want to post that new workbook to one of the
free file hosting services so we can see what you're working with:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"Lumi" wrote in message
...
No the formula did not work.
"Ron Coderre" wrote:
OK...Let's troubleshoot.
First we'll make sure the method *can* work:
1) Open a new workbook
2) Create the scenario by ENTERING the source values
and formulas (not copy/pasting).
Do the formulas behave?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"Lumi" wrote in message
...
Ron, thank you for the response
I did the test and still display the same date when I enter the
=TEXT(TRIM(V2),"yyyy-mm-dd h:mmam/pm") you suggested. In column v2
displays
11/8/2007 4:43:05 PM and on column w2 displays 11/8/2007 4:43:05 PM.
"Ron Coderre" wrote:
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.