|
How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using
excel. |
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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. |
THANK YOU! That worked great!
"Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. |
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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. |
Use a helper column with =MONTH(A1) copied down
Copy PasteSpecial Values, then sort on the helper column......... Vaya con Dios, Chuck, CABGx3 "brewisc13" wrote in message ... 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. |
Another option...
Use a helper cell with this formula: =text(a1,"mmyyyy") And fill down. brewisc13 wrote: 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. -- Dave Peterson |
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. -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "brewisc13" wrote in message ... 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. |
It sorts by Year.
"Dave Peterson" wrote: Another option... Use a helper cell with this formula: =text(a1,"mmyyyy") And fill down. brewisc13 wrote: 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. -- 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. brewisc13 wrote: It sorts by Year. "Dave Peterson" wrote: Another option... Use a helper cell with this formula: =text(a1,"mmyyyy") And fill down. brewisc13 wrote: 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. -- Dave Peterson -- Dave Peterson |
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. "Dave Peterson" wrote: =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. brewisc13 wrote: It sorts by Year. "Dave Peterson" wrote: Another option... Use a helper cell with this formula: =text(a1,"mmyyyy") And fill down. brewisc13 wrote: 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. -- Dave Peterson -- Dave Peterson |
On Wed, 22 Jun 2005 15:54:02 -0700, Brewisc13
wrote: We are trying to find out how many birthdays fall with in a given month using excel. 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 |
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. brewisc13 wrote: 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. "Dave Peterson" wrote: =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. brewisc13 wrote: It sorts by Year. "Dave Peterson" wrote: Another option... Use a helper cell with this formula: =text(a1,"mmyyyy") And fill down. brewisc13 wrote: 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. "brewisc13" wrote: THANK YOU! That worked great! "Earl Kiosterud" wrote: 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." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" wrote in message ... We are trying to find out how many birthdays fall with in a given month using excel. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 -- daumj ------------------------------------------------------------------------ daumj's Profile: http://www.excelforum.com/member.php...o&userid=25000 View this thread: http://www.excelforum.com/showthread...hreadid=381439 |
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. daumj wrote: 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 -- daumj ------------------------------------------------------------------------ daumj's Profile: http://www.excelforum.com/member.php...o&userid=25000 View this thread: http://www.excelforum.com/showthread...hreadid=381439 -- Dave Peterson |
| All times are GMT +1. The time now is 01:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com