Elimate year (either YY or YYYY) in data

[Jack]

I want to format a column ONLY using day and month so that I can sort
by that column. The default includes the year I have entered the data
(i.e. 2004 or 2005) which corrupts the sort. Anyway to remove it? I
have tried custom and special under format. Doesn't work. Thanks.

Jack

 

[Karl Burrows]

Why doesn't MMM DD, MMMM DD or M/DD work in custom formatting?

I want to format a column ONLY using day and month so that I can sort
by that column. The default includes the year I have entered the data
(i.e. 2004 or 2005) which corrupts the sort. Anyway to remove it? I
have tried custom and special under format. Doesn't work. Thanks.

Jack

 

[Harald Staff]

Hi Jack

Excel is not Wysiwyg. Formatting excel cells changes presentation only, it
does NOT change the actual content. So the years will be in there no matter
how you format the cells.

HTH. Best wishes Harald

 

[Bernie Deitrick]

JAck,

Use one (or two) helper columns. In the first, use a formula like

=MONTH(A1)

and in the second, use

=DAY(A1)

Then copy down to match your data, and sort based on the Month formula
column first, and the Day formula column second.

The single helper column could be:

=DATE(2005,MONTH(A1),DAY(A1))

HTH,
Bernie
MS Excel MVP