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.

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.

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.

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.

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.

Yes, I am working with Excel 2003. It return FALSE. I uploade the data on the
website named test.xls
I thank you so much for your assistance in trying to resolve this issue.

"Ron Coderre" wrote:

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.

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
"Ron Coderre" wrote:

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.

Hi, Lumi

None of those sites allows you to search for files.
You'll need to post the link to the file location.

--------------------------

Regards,

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

"Lumi" wrote in message
...
Yes, I am working with Excel 2003. It return FALSE. I uploade the data on
the
website named test.xls
I thank you so much for your assistance in trying to resolve this issue.

"Ron Coderre" wrote:

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.

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)


"Lumi" wrote in message
...
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
"Ron Coderre" wrote:

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.

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.

"Ron Coderre" wrote:

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)


"Lumi" wrote in message
...
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
"Ron Coderre" wrote:

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.

I'm wondering if there may be a problem with your regional settings.

From the Start Menu
....Select Control_Panel
....Dbl-Click: Regional and Language Options

See if anything looks irregular.

If NO
....Click the [Customize] button and see if those settings make sense.

Please let us know what you find.
--------------------------

Regards,

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



"Lumi" wrote in message
...
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.

"Ron Coderre" wrote:

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)


"Lumi" wrote in message
...
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
"Ron Coderre" wrote:

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.

Ron, first I want to thank you very much for all your support. Your
suggestion on going to regional settings was the problem. I compared the
sttings with another computer to find the discrepancies on the setings. So on
the Regional and Language Options on the Regional Options tab, the Short date
displayed 2007/12/29 and Longdate displayed 2007, December29 Saturday. I
Change it to be the Sort date 12/29/2007 and Long date to be Saturday,
December 29 2007. Then I went to Excel and selected the column,with dates
click on Data, Text to column, Finish and that change my dates. I hope other
people benefit from this. Again thank you for all your great input, patience
and great troubleshoot skills.
Sincerely
Lumi

"Ron Coderre" wrote:

I'm wondering if there may be a problem with your regional settings.

From the Start Menu
....Select Control_Panel
....Dbl-Click: Regional and Language Options

See if anything looks irregular.

If NO
....Click the [Customize] button and see if those settings make sense.

Please let us know what you find.
--------------------------

Regards,

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



"Lumi" wrote in message
...
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.

"Ron Coderre" wrote:

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)


"Lumi" wrote in message
...
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
"Ron Coderre" wrote:

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.

I'm glad you figured it out!....Thanks for the feedback


***********
Regards,
Ron

XL2003, WinXP


"Lumi" wrote:

Ron, first I want to thank you very much for all your support. Your
suggestion on going to regional settings was the problem. I compared the
sttings with another computer to find the discrepancies on the setings. So on
the Regional and Language Options on the Regional Options tab, the Short date
displayed 2007/12/29 and Longdate displayed 2007, December29 Saturday. I
Change it to be the Sort date 12/29/2007 and Long date to be Saturday,
December 29 2007. Then I went to Excel and selected the column,with dates
click on Data, Text to column, Finish and that change my dates. I hope other
people benefit from this. Again thank you for all your great input, patience
and great troubleshoot skills.
Sincerely
Lumi

"Ron Coderre" wrote:

I'm wondering if there may be a problem with your regional settings.

From the Start Menu
....Select Control_Panel
....Dbl-Click: Regional and Language Options

See if anything looks irregular.

If NO
....Click the [Customize] button and see if those settings make sense.

Please let us know what you find.
--------------------------

Regards,

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



"Lumi" wrote in message
...
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.

"Ron Coderre" wrote:

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)


"Lumi" wrote in message
...
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
"Ron Coderre" wrote:

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.