Unfortunately, I had try this and it does not change it. You think is any
other setting? As part of the test, if I enter 11-29-2007 it does
recognize
it as number, but enter 11/29/2007 does not recognize it as number. so I
wonder if the / are cousing problems and don't know how to convert this
since
I am exporting this from another source.
:
Thanks for posting the link......That's exactly what was needed!
The Col_A data is NOT numeric, although it is forced to be right-aligned.
To actually convert it...
1)Select the "dates"
2)From the Excel Main Menu
<data><text-to-columns>....Click [Finish]
Note, though, the forumula in B2 (on my computer) is
returning the correct value either way:
2007-11-08 4:43PM
But, in case it wasn't on your computer, the
text-to-columns method should correct that.
Does it?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
direct link
http://www.freefilehosting.net/download/39h8c
Html code<a
href="
http://www.freefilehosting.net/files/39h8c">test.xls</a>
direct link
test.xls
:
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)
No the formula did not work.
:
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)
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.
:
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)
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!
:
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)
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.
