I am working on a huge Excel 2003 workbook with many worksheets. Initially I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to be
like 12/3/07 but I don't want to have to type it all in manually if I don't
have to. If I go to format and go to date it seems you can select that format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

Hi

If excel see the dates as a date, select the column(s), goto Format and
change to the desired date format.

Hopes it helps

Per

"Kathy" skrev i meddelelsen
...
I am working on a huge Excel 2003 workbook with many worksheets. Initially
I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to
be
like 12/3/07 but I don't want to have to type it all in manually if I
don't
have to. If I go to format and go to date it seems you can select that
format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

Two ways:

1. select the column of text-dates and use Text to Columns to convert them
to numeric dates (in-place)

2. With Dec 3, 2009 in A1, enter in B1:

=DATEVALUE(A1) and copy down. Then format the values in column B as Datte.
--
Gary''s Student - gsnu200824


"Kathy" wrote:

I am working on a huge Excel 2003 workbook with many worksheets. Initially I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to be
like 12/3/07 but I don't want to have to type it all in manually if I don't
have to. If I go to format and go to date it seems you can select that format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

Select your column of data.
Data/ Text to Columns/ Delimited/ leave it at its default (delimited by
tab)/ at step 3 of 3 in the Wizard set Column date format to MDY/ Finish.
Then format your new date to suit (m/d/yy).
--
David Biddulph

Kathy wrote:
I am working on a huge Excel 2003 workbook with many worksheets.
Initially I am downloading the data from another database into Excel.
All the dates (thousands of them) are written like Dec 3, 2007. I
want to change them to be like 12/3/07 but I don't want to have to
type it all in manually if I don't have to. If I go to format and go
to date it seems you can select that format but it doesn't change
what is already in the downloaded data. Is there a simple way to do
this? Thanks.

hi
it would seem that the dates were import as text.
try this.
somewhere off to the side, enter a one into a cell. copy it. highlight the
date(s) that you wish to convert to numbers. then Paste Special Multiply.
the text date should convert to the serial date number ie 39419. you can
then reformat anyway you wish.

Regards
FSt1

"Kathy" wrote:

I am working on a huge Excel 2003 workbook with many worksheets. Initially I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to be
like 12/3/07 but I don't want to have to type it all in manually if I don't
have to. If I go to format and go to date it seems you can select that format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

If the formula bar displays the same date in a different format when in this
cell, all should be well, dates are dates. If not, see if the function
=DATEVALUE(A1)
does the trick in B1, with the troublesome date in A1.

HTH. Best wishes Harald


"Kathy" wrote in message
...
I am working on a huge Excel 2003 workbook with many worksheets. Initially
I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to
be
like 12/3/07 but I don't want to have to type it all in manually if I
don't
have to. If I go to format and go to date it seems you can select that
format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

Thank all of you who gave me advice. I couldn't get any of those options to
work but found a way and thought I'd share it in case someone else has this
problem. I select the column then Edit/Replace. Once the replace thing is up
I type in 1spacebar in the first line and then 1,spacebra in the second line.
When I say spacebar I mean I press the spacebar. This covers all the dates
ending with 1 such as Dec 1 2007. Then I type in 2spacebar and 2,spacebar and
that converts the dates ending with 2 such as Dec 2 2007 and so forth. This
is the only thing that worked with the data I have. Perhaps it will help you
some time as well.
"Kathy" wrote:

I am working on a huge Excel 2003 workbook with many worksheets. Initially I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to be
like 12/3/07 but I don't want to have to type it all in manually if I don't
have to. If I go to format and go to date it seems you can select that format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.

Thanks for feeding back, Kathy - it's always useful to find out how a
problem has been solved.

However, it looks like you did not have commas in your original dates
(unlike your posted example), and if contributors had known this they
could have suggested appropriate formulae to get the dates recognised,
rather than having to apply your Find & Replace method ten times.

Pete

On Jan 7, 10:45*pm, Kathy wrote:
Thank all of you who gave me advice. I couldn't get any of those options to
work but found a way and thought I'd share it in case someone else has this
problem. I select the column then Edit/Replace. Once the replace thing is up
I type in 1spacebar in the first line and then 1,spacebra in the second line.
When I say spacebar I mean I press the spacebar. This covers all the dates
ending with 1 such as Dec 1 2007. Then I type in 2spacebar and 2,spacebar and
that converts the dates ending with 2 such as Dec 2 2007 and so forth. This
is the only thing that worked with the data I have. Perhaps it will help you
some time as well.



"Kathy" wrote:
I am working on a huge Excel 2003 workbook with many worksheets. Initially I
am downloading the data from another database into Excel. All the dates
(thousands of them) are written like Dec 3, 2007. I want to change them to be
like 12/3/07 but I don't want to have to type it all in manually if I don't
have to. If I go to format and go to date it seems you can select that format
but it doesn't change what is already in the downloaded data. Is there a
simple way to do this? Thanks.- Hide quoted text -

- Show quoted text -