Sort Dates

[pete5440]

I have a worksheet with one column being dates in the format of 3/2, 4/25,
12/10, etc. As I added dates I could always sort from lowest (1/1) to highest
(12/31). Now I can't seem to sort any dates that have been added in 2009. I
do not have a year attached to these dates as they are birthdays and I need
to sort by month/day regardless of year. Why all of a sudden the problem and
how do I fix?

 

[Mike H]

Hi,

It happening because your dates are proper dates and the sort is including
the year even though you cant see it.

You need a helper column and put this formula in it

=DAY(A1)&MONTH(A1)

Drag down to the same length as your date and then selet both columns and
sort on the kelper column which you can hide if you want.

Mike

 

[Gord Dibben]

If no year associated, those are not dates but simply numbers entered as
text.

Will sort as text only.

A real Excel date is a serial number and sorts in that serial number order.

January 16, 2008 is serial number 39829


Gord Dibben MS Excel MVP

 

[pete5440]

Mike, First of all I'm not familiar with a "Helper Column". But will this
formula "fix" my issue, and will I have to keep the helper column and use it
to sort my date column from here on?

Gord,

If my numbers are "text" why are my sort options "Oldest to Newest" and not
"A-Z"? This sounds like the simpler method but how do I let excel know this
is (supposed to be) text?

 

[Gord Dibben]

Oldest to Newest must be a 2007 function.

I am not familiar with it.

I still believe you do not have real dates but perhaps 2007 treats these
differently than earlier versions.


Gord

 

[pete5440]

Yes, this is 2007.

Oldest to Newest must be a 2007 function.

I am not familiar with it.

I still believe you do not have real dates but perhaps 2007 treats these
differently than earlier versions.


Gord

 

[pete5440]

If I highlight A2 (A1 is my column heading) the formula box shows 1/1/2008. I
have never set up the year and there is no formula set up as I have only ever
added the month/day as "1/17"

 

[Gord Dibben]

What you see depends upon your Regional Settings

Mine are set for dd/mmm/yy

I type 1/17 and formula bar shows jan 1, 2017

I type 17/1 and formula bar shows jan 17, 2009

1/12 shows dec 1, 2009

1/13 shows jan 1, 2013

See the pattern?

If the month/day are ambiguous, Excel chooses from the Regional settings

There is no 17th month so Excel thinks that must be the year.


Gord

 

[Gord Dibben]

Actually the word "ambiguous" is incorrect.

More like if larger number is greater than 12 then Excel interprets that
number as a year depending upon Regional settings being dd/mmm'yy


Gord