Excel 2000 worksheet where I want to convert date format

[Guest]

I have a worksheet (in 2000) that was sent to me. The information in the
worksheet was culled from an Access database that I have no access to, and
saved to Excel.

There are several columns of dates that came over as "general" in this format:
20031125.
I want to quickly convert this into the proper date format of 11/25/03, but
I cannot seem to figure out a quick method to do it in Excel 2000.
HELP!

 

[Guest]

Do you ALWAYS have four digits for the year, and two each for month and day?
If so, I'd use Data > Text to Columns, with the delimited option to break
each yyyymmdd into its components, then =date(a1,b1,c1) to form the date.
Finally copy/ paste special values to lock in the results.

 

[Guest]

Yes, the sheet always has 4 digits for year and two each for month and date.
I don't understand what you mean to use Data>text to columns, etc. etc. Is
this a formula or a fomatting condition? Sorry, could you be more specific
on how to do this?

Thanks!

 

[Guest]

I think you can do what you want all in one step:

Select the "date" cells
Data>Text-to-columns
Delimited...Click Next
(Doesn't matter what the delimiter is)...Click Next
Click the Date option and set the dropdown to YMD...Click Finish
That should turn those numbers into dates.

Does that help?

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

XL2002, WinXP-Pro

 

[Max]

.. If so, I'd use Data > Text to Columns,
with the delimited option to break
each yyyymmdd into its components ...

Perhaps with Data > Text to Columns there's no need to break further ? Just
select the source col of "dates", click Data > Text to Columns, click Next >
Next, and in Step 3 (under "Column data format"): check "Date", select "YMD"
from the droplist, click Finish. Then just format the converted col as
"Date" (to taste).

 

[Guest]

So very cool!
Thanks for the help!

I think you can do what you want all in one step:

Select the "date" cells
Data>Text-to-columns
Delimited...Click Next
(Doesn't matter what the delimiter is)...Click Next
Click the Date option and set the dropdown to YMD...Click Finish
That should turn those numbers into dates.

Does that help?

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

XL2002, WinXP-Pro