HOW DO I SET A WORKSHEET TO TAKE 020305 AND CHANGE IT TO 02/03/05. I just
need a date not a serial number.

format your cell
select the cell
right click on the mouse, select format cells
go to numbers,date, select the format you want


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=522417

daves excel

How will that insert the slashes?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"davesexcel" wrote
in message ...

format your cell
select the cell
right click on the mouse, select format cells
go to numbers,date, select the format you want


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile:
http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=522417

How will that insert the slashes?

Well, when I tested it yesterday, I thought it worked, Now it doesn't,
obviously I was wrong.


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=522417

You can't without code. (Probably a worksheet_change() event), you can use a
helper column though if the numbers are all 8 digits long (It can still be
done if they are shorter but we will need to accont for this. Let's say the
first number is in A1, in B1 enter

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))

Copy down and format the resulting numbers as a dates. You can then copy
the new dates and EditPaste special...Values over the original numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"jgregory" wrote in message
...
HOW DO I SET A WORKSHEET TO TAKE 020305 AND CHANGE IT TO 02/03/05. I just
need a date not a serial number.

Assuming (1) the leading zero is really present, and (2) use are using US
date format (mm/dd/yy)
then use =DATE(2000+RIGHT(A1,2),LEFT(A1,2),MID(A1,4,2))

Pleases note there is no difference between a date and a serial number other
than the applied format.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jgregory" wrote in message
...
HOW DO I SET A WORKSHEET TO TAKE 020305 AND CHANGE IT TO 02/03/05. I just
need a date not a serial number.