DATE SET

J

jgregory

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.
 
D

davesexcel

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

Nick Hodge

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 Edit>Paste special...>Values over the original numbers

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]
 
B

Bernard Liengme

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.
 
D

davesexcel

How will that insert the slashes?

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