Converting 20010321 to 03/21

H

Hawkins,K

I've tried every type of format available and tried to custom make one.
Please help! I have a whole column of birthdates listed as 20010321
(yyyymmdd) and I need them to read only the mm/dd. General steps
appreciated. thx!
 
P

Peo Sjoblom

Select the column, do data>text to columns, click next twice, under column
data format select Date and from dropdown select YMD and click finish, with
the column still selected do format>cells>number>custom and type mm/dd in
the type box


Note that the underlying value will always be a full date (serial number)
 
D

Duke Carey

Assuming your # is in A2, try this in B2

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

format B2 as mmm-dd
 
H

Harlan Grove

Hawkins said:
I've tried every type of format available and tried to custom make one.
Please help! I have a whole column of birthdates listed as 20010321
(yyyymmdd) and I need them to read only the mm/dd. General steps
appreciated. thx!
Click to expand...

Select the column of birthdates, run the menu command Data > Text to
Columns, select Fixed Width, click the Next button twice to move to the 3rd
step of the wizard, choose Date in the Choose data format section, and use
the drop-down list to the right of Date to select YMD, then click the Finish
button.
 
M

Michael

Select the column where the data is from the main toolbar menu select data
->text to columns(Click on Delimited) , click next twice, on your Top right
select DATE from the data format and from dropdown select YMD and click
finish,
On the same column now select Format -> Cells -> Custom-> and type mm/dd
 
H

Hawkins,K

Michael, you are pure genius..... (I'm sure the other methods would have
worked but I tried yours first. thanks so much)
 
Top