How do I eliminate the year in mm/dd/yyyy?

[adminbfc]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am working with a column of dates that I need to sort by month/date, disregarding the year. Each time I add (for example) 11/3, when I click back in the cell it says 11/3/2009. I need to eliminate the year entirely, or make a custom date so that the year is always the current year.

I have clicked on an empty cell and then clicked on 3/14 in NUMBER/DATE; I have clicked on NUMBER/CUSTOM/m/d;@. I have tried NUMBER TEXT, but it sorts by 1/1 (Jan 1), 10/1 (October 1), 2/1 (February 1)d and I need the months to follow their normal progression.

Any help is appreciated.

 

[CyberTaz]

Just in case you haven't realized it yet the DATE & Number formatting won't
work because there is no such thing as a Day & Month that isn't part of a
Year... but you're on the right track by formatting as Text You just
have to go one step further because in a text string the character 2 has a
higher value than the character 1 even if the 1 is followed by zeroes -- the
zeroes (or any characters) are separate.

Go into Excel> Preferences> Lists & create a custom list by entering the
series of months 1 thru 12. When you want to sort the data use Data> Sort &
click the Custom button in the lower left corner of the Sort dialog in order
to invoke your custom sort list.

 

[Jeff Chapman]

Hello,

I am working with a column of dates that I need to sort by month/date,
disregarding the year. Each time I add (for example) 11/3, when I click back
in the cell it says 11/3/2009. I need to eliminate the year entirely, or make
a custom date so that the year is always the current year.

I have clicked on an empty cell and then clicked on 3/14 in NUMBER/DATE; I
have clicked on NUMBER/CUSTOM/m/d;@. I have tried NUMBER TEXT, but it sorts by
1/1 (Jan 1), 10/1 (October 1), 2/1 (February 1)d and I need the months to
follow their normal progression.

If you are inputting your months and days as dates (for example,
yyyy.mm.dd as the likes) and not as text, you can select the text to sort,
and then use the Data - Sort - Options feature, Custom Sort Order
to reorder your data in chronological order.

It's a little like what Bob suggested in his last post, but
the method he suggested assumes that you have inputted your list
of days and months as _text_ (i.e., the cell is formatted as "Text"),
not as _date_. There is a difference, I believe.
(Actually, Excel already has the "custom list" of months available
by default, so there appears to be no need to type them in - just select
the series you want to use.)

Jeff

 

[CyberTaz]

Hi Jeff;

Hello,



If you are inputting your months and days as dates (for example,
yyyy.mm.dd as the likes) and not as text, you can select the text to sort,
and then use the Data - Sort - Options feature, Custom Sort Order
to reorder your data in chronological order.

It's a little like what Bob suggested in his last post, but
the method he suggested assumes that you have inputted your list
of days and months as _text_ (i.e., the cell is formatted as "Text"),
not as _date_. There is a difference, I believe.
(Actually, Excel already has the "custom list" of months available
by default, so there appears to be no need to type them in - just select
the series you want to use.)

Jeff

Unfortunately -- based on my understanding of what the OP described in the
statement "I need to eliminate the year entirely,..." -- your suggestion
won't work. Have you tried it?

If the cell is formatted for DATE there *must* be a year even if the cell is
*formatted* to not display it. If you don't enter one the program will
assume the current year. What's actually stored by the sheet is a serial
number predicated on all 3 parts a true "date" must have. Therefore, even
when the Sort by Month is used the year part is taken into consideration and
all dates within the same year will be in the right order but (i.e.) July of
1999 will be separated from July 2006 if there are any dates for the
intervening years. IOW, even sorting by month the system sorts first by year
then by month then by day (then by hr, min, sec).

That isn't what the OP asked for & the built-in custom sorts for month don't
work if the cells contain anything other than the month name. (January or
Jan is OK, but January 15 is not.) What s/he could do would be to put the
month name in one cell & the day in another & use the custom sort by month
name to sort the list based on the month column... Assuming s/he is willing
to use the text names or abbreviations for the month data rather than the
numerical equivalent indicated in the post.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Jeff Chapman]

Hello Bob,

Unfortunately -- based on my understanding of what the OP described in the
statement "I need to eliminate the year entirely,..." -- your suggestion
won't work.

I see... I was actually working under the assumption that
the user would input the year values as well, even though
the year values would not be actually displayed due to using
a custom number format like mmm.dd or the likes.

Jeff