Simple way to change text dates to numerical?

[Kathy]

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.

 

[Per Jessen]

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

 

[Gary''s Student]

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.

 

[FSt1]

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

 

[Harald Staff]

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]

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.

 

[Pete_UK]

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