Converting 20010321 to 03/21
 

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!

Converting 20010321 to 03/21
 

Select the column, do datatext 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 formatcellsnumbercustom and type mm/dd in
the type box


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


--
Regards,

Peo Sjoblom



"Hawkins,K" wrote in message
...
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!

Converting 20010321 to 03/21
 

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

"Hawkins,K" wrote:

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!

Converting 20010321 to 03/21
 

"Hawkins,K" wrote...
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!

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.

Converting 20010321 to 03/21
 

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

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Hawkins,K" wrote:

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!

Converting 20010321 to 03/21
 

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

"Michael" wrote:

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

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Hawkins,K" wrote:

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!