In Excel, how do I totally delete year in date field?

[B. Vincent]

I have a row of date data including month, day and year. I want to delete
the year, not just reformat it. My final goal is to put these birthdays into
Outlook so I can track a large number of people's birthdays. When I put them
into Outlook now, I have trouble sorting them because of the year. I also
cannot get the reminder window to work. I purchased software to do it but it
has failed and I am not getting any support from the software creators.

Please Help.

 

[James Silverton]

B. wrote on Sat, 23 Jul 2005 04:51:02 -0700:

BV> I have a row of date data including month, day and year. I
BV> want to delete the year, not just reformat it. My final
BV> goal is to put these birthdays into Outlook so I can track
BV> a large number of people's birthdays. When I put them into
BV> Outlook now, I have trouble sorting them because of the
BV> year.
BV> Please Help.

Perhaps, there is a more elegant approach but this seems to
work. Sorting in Excel would be easier if the dates were in a
column, say A. You could then make a helper column with, say,
=100*MONTH(a1)+DAY(a1) etc., format as number and sort.

James Silverton.

 

[B. Vincent]

Thank you for your response. When I sort the data, it sorts by year first.
I want to get rid of the year. I want all my Jan 1st to be together,
regardless of the year they were born. I am sorry if I was not clear earlier.
Thanks again!

 

[James Silverton]

B. wrote on Sat, 23 Jul 2005 07:33:04 -0700:

BV> "James Silverton" wrote:

??>> B. wrote on Sat, 23 Jul 2005 04:51:02 -0700:
??>>
BV>>> I have a row of date data including month, day and year.
BV>>> I want to delete the year, not just reformat it. My
BV>>> final goal is to put these birthdays into Outlook so I
BV>>> can track a large number of people's birthdays. When I
BV>>> put them into Outlook now, I have trouble sorting them
BV>>> because of the year. Please Help.
??>>
??>> Perhaps, there is a more elegant approach but this seems
??>> to work. Sorting in Excel would be easier if the dates
??>> were in a column, say A. You could then make a helper
??>> column with, say, =100*MONTH(a1)+DAY(a1) etc., format as
??>> number and sort.
??>>
??>> James Silverton.
??>>

Sorry, I mistyped! Formatting as GENERAL in the helper column is
what I should have said and then the year disappears. A little
test seems to work.



James Silverton.

 

[Beth Melton]

If the data is a date then there really isn't a way to delete the
"year" simply due to how a date is stored. You're actually working
with a serial number which represents the number of days since January
0, 1900. 1/1/1099 is day 1. Today is 38558. The date formats simply
place the date into a format we understand.

What you need to do is use a couple functions that extract the Month
and Day of the date, similar formula James provided:

=Month(A1)&"/"&Day(A1)

Note you can use any character between the quotes. I used the forward
slash (/) since that's a common delimiter.Sort the column containing
the extracted month and day and you should get the result you are
after.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/