Using the sort function for a date column where year was not enter

[Grosvenor]

I have a "Birthday" column in a spreadsheet where only the month/day is
entered. When sorting on this column, the month/day is sorted by the year
the date was entered even though no year was entered. There appears to be a
defaulting setting somewhere, but cannot determined where to adjust the
setting so the column is sorted on only month/year regardless of year
entered.

 

[Mike H]

Hi,

First I assume when entering these dates you enter something like 1/4 for 1
April. If you do this Excel will automatically add the current year.

Or possibly a full date has been entered 1/4/2001 and it is formatted to
only show the Day/Month. In either case the year is still there because
formatting doesn't change the underlying value so to sort By day/Month try
this

in a helper column insert the formula
=TEXT(A1,"mmmdd") and drag down
Sort by helper column which you can hide if you want.

Mike

 

[Niek Otten]

Dates are always stored including a year.
Use a helper column as sort key.
If you have dates like 0101:
=month(a1)&day(a1)
If you prefer month names:
=DATE(1904,Month(a1),day(a1)) and format as mmm dd
The use of 1904 is to enable Feb 29 dates

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a "Birthday" column in a spreadsheet where only the month/day is
| entered. When sorting on this column, the month/day is sorted by the year
| the date was entered even though no year was entered. There appears to be a
| defaulting setting somewhere, but cannot determined where to adjust the
| setting so the column is sorted on only month/year regardless of year
| entered.

 

[Grosvenor]

This explains everything! Thank you very much!

 

[Fred Smith]

Sorting mmmdd cells will not put them in chronological order because they
will be sorted alphabetically by month name (so, April will be first,
followed by August, etc.).

Use a format of "mmdd" if you want to follow this procedure.

Regards,
Fred.