I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office
7. The worksheet has over 7000 rows, one of which contains dates. The dates
have shown up a 5 digit numbers. I formatted the date column to show dates
as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09
format. The rest seemed to be locked in the 5 digit number format and can't
be changed. I tried to delete a few cells and manually type in dates. The
cells remained as 5 digit numbers.
Any sugesstions would be appreciated.
--
Bob Reddy

On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy
wrote:

I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office
7. The worksheet has over 7000 rows, one of which contains dates. The dates
have shown up a 5 digit numbers. I formatted the date column to show dates
as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09
format. The rest seemed to be locked in the 5 digit number format and can't
be changed. I tried to delete a few cells and manually type in dates. The
cells remained as 5 digit numbers.
Any sugesstions would be appreciated.

Make sure the sheet is unprotected, and highlight the entire column
when you perform the format operation.

The dates might have been saved improperly as text. Try filling out a
function in the next open column with this function =D2*1 (if the date is
infact in d)..

Fill this down and then copy this column over the top of the old one as
values (paste special, values).

What this does is take the date number "40000" and turn it into a number
which excel can format as a date "mm/dd/yyyy".

TFT

"CellShocked" wrote:

On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy
wrote:

I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office
7. The worksheet has over 7000 rows, one of which contains dates. The dates
have shown up a 5 digit numbers. I formatted the date column to show dates
as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09
format. The rest seemed to be locked in the 5 digit number format and can't
be changed. I tried to delete a few cells and manually type in dates. The
cells remained as 5 digit numbers.
Any sugesstions would be appreciated.

Make sure the sheet is unprotected, and highlight the entire column
when you perform the format operation.
.

Hi Troy,

Thanks for posting your response - that was so helpful to me.
I recently imported data from Excel into Access and the dates changed to
5-digit numbers. I had no idea how to sort it out and all the information
online seems to relate to converting 6-digit numbers. Your tip really did the
trick. Many thanks.
R

"TroyT" wrote:

The dates might have been saved improperly as text. Try filling out a
function in the next open column with this function =D2*1 (if the date is
infact in d)..

Fill this down and then copy this column over the top of the old one as
values (paste special, values).

What this does is take the date number "40000" and turn it into a number
which excel can format as a date "mm/dd/yyyy".

TFT

"CellShocked" wrote:

On Wed, 11 Nov 2009 19:03:02 -0800, Bob Reddy
wrote:

I copied a xls worksheet from an Excel 2003 to a new PC with Vista and Office
7. The worksheet has over 7000 rows, one of which contains dates. The dates
have shown up a 5 digit numbers. I formatted the date column to show dates
as 1/1/09 formats. Only 15% to 20% of the cells changed to the 1/1/09
format. The rest seemed to be locked in the 5 digit number format and can't
be changed. I tried to delete a few cells and manually type in dates. The
cells remained as 5 digit numbers.
Any sugesstions would be appreciated.

Make sure the sheet is unprotected, and highlight the entire column
when you perform the format operation.
.