I have a file that was sent to me with a column of dates that appear like
20070602. I need it to look like 06/02/2007. I have tried to format it but
I get ########### and the screen says something about a negative number. If
I open a new blank worksheet and type it in 20070602, Excel automatically
switches it to a proper date format. When I receive the file with the date
as 20070602, it is already saved in excel. I have tried several different
formats. Any help would be greatly appreciated.

Thanks

Jm

First, select a cell in that column.
Look at the formulabar.

Do you see a real date or 20070602?

If you see a real date (06/02/2007 or 02/06/2007), then just reformat it the way
you want.

Select the column
format|cells|number tab|date (or custom)

If you see a number, then it's not really a date (yet). It's just a number.

Select that column
data|Text to columns
fixed width (but don't split it into any fields)
choose ymd (or ydm???) and finish up.

Then format it the way you want.

======
By the way, I've never seen excel change 20070602 into a date when you type it
in. You either have a macro that does the work--or you're typing a date (with
/'s or -'s).



JCM wrote:

I have a file that was sent to me with a column of dates that appear like
20070602. I need it to look like 06/02/2007. I have tried to format it but
I get ########### and the screen says something about a negative number. If
I open a new blank worksheet and type it in 20070602, Excel automatically
switches it to a proper date format. When I receive the file with the date
as 20070602, it is already saved in excel. I have tried several different
formats. Any help would be greatly appreciated.

Thanks

Jm

--

Dave Peterson

Thanks, this really helped me and worked perfect. By the way, you are
correct about my comment on Excel changing the number to a date. I was
typing in a / between the numbers.

Thanks again!

"Dave Peterson" wrote:

First, select a cell in that column.
Look at the formulabar.

Do you see a real date or 20070602?

If you see a real date (06/02/2007 or 02/06/2007), then just reformat it the way
you want.

Select the column
format|cells|number tab|date (or custom)

If you see a number, then it's not really a date (yet). It's just a number.

Select that column
data|Text to columns
fixed width (but don't split it into any fields)
choose ymd (or ydm???) and finish up.

Then format it the way you want.

======
By the way, I've never seen excel change 20070602 into a date when you type it
in. You either have a macro that does the work--or you're typing a date (with
/'s or -'s).



JCM wrote:

I have a file that was sent to me with a column of dates that appear like
20070602. I need it to look like 06/02/2007. I have tried to format it but
I get ########### and the screen says something about a negative number. If
I open a new blank worksheet and type it in 20070602, Excel automatically
switches it to a proper date format. When I receive the file with the date
as 20070602, it is already saved in excel. I have tried several different
formats. Any help would be greatly appreciated.

Thanks

Jm

--

Dave Peterson