software

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: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=522417You 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)amp;quot;/quot;amp;MID(A1,3,2)amp;quot;/quot;amp;RIGHT(A1,2))

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HISquot;jgregoryquot; gt; wrote in message
...
gt; HOW DO I SET A WORKSHEET TO TAKE 020305 AND CHANGE IT TO 02/03/05. I just
gt; 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

quot;jgregoryquot; gt; wrote in message
...
gt; HOW DO I SET A WORKSHEET TO TAKE 020305 AND CHANGE IT TO 02/03/05. I just
gt; need a date not a serial number.
daves excel

How will that insert the slashes?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HISquot;davesexcelquot; gt; wrote
in message ...
gt;
gt; format your cell
gt; select the cell
gt; right click on the mouse, select format cells
gt; go to numbers,date, select the format you want
gt;
gt;
gt; --
gt; davesexcel
gt; ------------------------------------------------------------------------
gt; davesexcel's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31708
gt; View this thread: www.excelforum.com/showthread...hreadid=522417
gt;

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: www.excelforum.com/member.php...oamp;userid=31708
View this thread: www.excelforum.com/showthread...hreadid=522417