How do you sort a date range by month?

B

Brewisc13

We are trying to find out how many birthdays fall with in a given month using
excel.
 
E

Earl Kiosterud

You could use a helper column, with something like = A2. Format this
(Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals,
"At each change in: Month" and "Use function: Count."
 
B

brewisc13

THANK YOU! That worked great!

Earl Kiosterud said:
You could use a helper column, with something like = A2. Format this
(Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals,
"At each change in: Month" and "Use function: Count."
Click to expand...
 
B

brewisc13

One more question: We have a range of birthdates, even when you format it to
mmm, you cant sort by month. We want to find out the people in the ragne whos
birthday falls in a given month.
 
C

CLR

Use a helper column with =MONTH(A1) copied down
Copy > PasteSpecial > Values, then sort on the helper column.........

Vaya con Dios,
Chuck, CABGx3
 
D

Dave Peterson

Another option...

Use a helper cell with this formula:

=text(a1,"mmyyyy")

And fill down.
 
E

Earl Kiosterud

I don't know why it wouldn't sort by month. It actually sorts by date, but
that should result month sequence, not alphabetical sequence by month. I
meant to say you should sort your original table before you do the
subtotals. I think you must have anyway.
 
D

Dave Peterson

=text(a1,"mmyyyy")

Sorts by years.

I see 062005 in that cell if A1 contains today's date.

I used the mnemonics for USA/English for month and year. If you're not using an
English version of excel, you'll have to use your language's abbreviations.

If you're not sure, post what language you're using.
 
B

brewisc13

I want to sort my range of dates by month....we have a range of birthdays and
we want to group all of the January birthdays together, Feb birthdays, etc.
 
R

Ron Rosenfeld

We are trying to find out how many birthdays fall with in a given month using
excel.
Click to expand...


Take a look at Pivot Tables.
Drag the Dates to the Row column
Drag Dates to the Data area
Right click on the column of dates and select Group and Show
Detail/Group and then select Months.


--ron
 
D

Dave Peterson

If I have a bunch of cells that look like this:

062005 (for June of 2005)

and I sort by that column, I get the months grouped together. I'm surprised you
don't. Maybe you could post the dates that cause the formula and sort to fail.



I want to sort my range of dates by month....we have a range of birthdays and
we want to group all of the January birthdays together, Feb birthdays, etc.
Click to expand...
 
D

daumj

I have been trying to do this in an easy macro.

I have a Name and DATE-OF-BIRTH list and want to automagically create
another worksheet where the month's birthdays are sorted by Jan, Feb,
Mar... etc. Right now, the best I can do is have it sort by year, then
Month. Try it out the suggested solutions on a list of birthdays: 2 Feb
2002 4 Mar 2003 28 Mar 2002 1 Apr 1999. I want to see a sort with
Feb-2 first, followed by Mar-4, then Mar-28, and finally Apr-1.

Any ideas?

John
 
D

Dave Peterson

I'd keep the data on the same sheet.

But add another column with a bunch of formulas:

=text(a2,"mmddyyyy")

And drag down.

Then you can sort by that column.
 
Top