How to insert text into a cell
 

I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch of
#### crap.

Thanks


BEN

Hi Benjamin

One way, with your date value in A1 would be
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change reference accordingly and copy down the sheet.

--
Regards
Roger Govier
"Benjamin Riley" <Benjamin wrote in message
...
I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch
of
#### crap.

Thanks


BEN

using =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) will change the look of
the cell

"Benjamin Riley" wrote:

I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch of
#### crap.

Thanks


BEN

Benjamin,

yyyymmdd isn't a date format Excel recognizes. So it just considers
19810305 a number (or maybe text, if it got imported that way). If one was
in A2, you could use:
=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))

This would yield a bona-fide date, which you could format in many ways with
Format - Cells - Number - Date tab (or Custom, and make your own format
code, mm/dd/yyyy in your example).

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Benjamin Riley" <Benjamin wrote in message
...
I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch
of
#### crap.

Thanks


BEN

Thanks for the help this works great

"Roger Govier" wrote:

Hi Benjamin

One way, with your date value in A1 would be
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change reference accordingly and copy down the sheet.

--
Regards
Roger Govier
"Benjamin Riley" <Benjamin wrote in message
...
I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch
of
#### crap.

Thanks


BEN

Thanks for the help. This works great.

"bigwheel" wrote:

using =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) will change the look of
the cell

"Benjamin Riley" wrote:

I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch of
#### crap.

Thanks


BEN

If your dates are in a nice column, select that range and do data|Text to
columns.

Choose fixed width
remove any lines that excel may have guessed
choose ymd as the date format
widen the column to see the date.
format it the way you like (mm/dd/yyyy)



Benjamin Riley wrote:

I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch of
#### crap.

Thanks

BEN

--

Dave Peterson