Need help sorting DATES please..

[Crackles McFarly]

I have a large amount of data, it's simply dates.
example
4/4/92
1/3/2007
5/6/1955
etc

I want to display ONLY the month and then SORT only by the month.

Basically I want to ignore the YEAR in the date and then sort.

I've tried this several ways BUT it will not sort the numbers even if
I format them.

help?

 

[Guest]

Insert a helper column next to your dates and with the dates in (say) A1 down
put this in b1 and drag down

=TEXT(A1,"mm/dd")

Select your dates and this helper column and sort by the helper column.

Mike

 

[Crackles McFarly]

I cannot get it to sort in either direction.
Can you help with that too?
thanks

 

[Crackles McFarly]

July-60
July-71
December-73
July-83
October-83
July-86
August-87
June-90
August-90
August-90
March-91
July-92
July-92
August-92
September-92
September-92
April-93

I need to Sort by the MONTH and strip the YEAR.
Then I need to simply Sort the column by Month only.

I don't know if that makes any sense.
thanks.

 

[Gord Dibben]

In an adjacent column enter =MONTH(A1) and drag/copy down.

Sort by that column.


Gord Dibben MS Excel MVP

 

[Crackles McFarly]

In an adjacent column enter =MONTH(A1) and drag/copy down.

Sort by that column.


Gord Dibben MS Excel MVP

That gives a number result, like 12 in place of December.

Also I cannot sort, the function doesn't work on the MONTH(A1)

Is their some setting that is not allowing me to Sort?
I'm using WORD 97 btw.

thanks.

 

[Gord Dibben]

Why do you need it formatted if all you're doing is sorting by?

Format column A to "mmmm" to see just the months in that column then hide B if
you don't want to see the numbers

I have no trouble sorting on column B tested with the dates you supplied below.

Select both columns before sorting by the MONTH column

I hope you're not using WORD 97.........this is an Excel news group.


Gord

 

[Crackles McFarly]

Why do you need it formatted if all you're doing is sorting by?

Format column A to "mmmm" to see just the months in that column then hide B if
you don't want to see the numbers

I have no trouble sorting on column B tested with the dates you supplied below.

Select both columns before sorting by the MONTH column

I hope you're not using WORD 97.........this is an Excel news group.

i SWEAR it will not sort in order of month when I do what you've said
to do.
Maybe you could give me some more hints, I don't know how to do this
it's obvious.

It was a mistake, yes I am of course using EXCEL 97 not WORD 97

thanks for helping.

 

[Gord Dibben]

Using your dates below in column A formatted as mmmm and =MONTH(A1) in column B
and copying down I get this returned.

July 7
July 7
December 12
July 7
October 10
July 7
August 8
June 6
August 8
August 8
March 3
July 7
July 7
August 8
September 9
September 9


After sorting both columns together by column B if get

March 3
June 6
July 7
July 7
July 7
July 7
July 7
July 7
August 8
August 8
August 8
August 8
September 9
September 9
October 10
December 12

Are you sure your dates are real dates and not text?


Gord

 

[Crackles McFarly]

Using your dates below in column A formatted as mmmm and =MONTH(A1) in column B
and copying down I get this returned.

July 7
July 7
December 12
July 7
October 10
July 7
August 8
June 6
August 8
August 8
March 3
July 7
July 7
August 8
September 9
September 9


After sorting both columns together by column B if get

March 3
June 6
July 7
July 7
July 7
July 7
July 7
July 7
August 8
August 8
August 8
August 8
September 9
September 9
October 10
December 12

Are you sure your dates are real dates and not text?


Gord

No it's formatted as a DATE, I guess I'll figure this old some day but
not this day.

I feel slightly retarded for not know how to do something this simple.

I "do" appreciate your patience and help though.

 

[Gord Dibben]

Just because it is formatted as a date doesn't mean it is a date.

When you change column A format to General what do you see?

If real dates you should see 5-digit numbers like

33298
33025
22098
26115
30498

Do you get numbers from the =MONTH() formula as I did in column B?


Gord

 

[Crackles McFarly]

Just because it is formatted as a date doesn't mean it is a date.

When you change column A format to General what do you see?

If real dates you should see 5-digit numbers like

33298
33025
22098
26115
30498

Do you get numbers from the =MONTH() formula as I did in column B?

I see 5 digit numbers as you do.