Filter by month, not year in Excel?
February 4, 2006 11:21 AM Subscribe
How do I sort a column of birthdates in Excel to by month and NOT year?
I have an Excel spreadsheet with customer information: last name, first, birthdate, etc. I need to sort by month, so I can send out birthday cards. But I can't seem to format it just to month and day; it assumes I mean that date in 2006. And when I try to filter it by "Begins with" with the month entered, I can't get it to work.
How do I accomplish my goal, here?
I have an Excel spreadsheet with customer information: last name, first, birthdate, etc. I need to sort by month, so I can send out birthday cards. But I can't seem to format it just to month and day; it assumes I mean that date in 2006. And when I try to filter it by "Begins with" with the month entered, I can't get it to work.
How do I accomplish my goal, here?
Using Excel's PivotTable thingy should also allow you to accomplish this.
posted by mwhybark at 11:42 AM on February 4, 2006
posted by mwhybark at 11:42 AM on February 4, 2006
Can you add another column to the spreadsheet? If so, use the Month function, i.e., if A2 has the birthdate in it, in the new column, you would enter "=Month(a2)" (without the quotes). Copy and paste that to all the other cells in that column, and then sort on that column.
posted by jasper411 at 11:46 AM on February 4, 2006 [1 favorite]
posted by jasper411 at 11:46 AM on February 4, 2006 [1 favorite]
If you're very careful you can delete the year, do your sort, print the results (or save into another spreadsheet), and close you original worksheet without saving.
posted by KneeDeep at 12:18 PM on February 4, 2006
posted by KneeDeep at 12:18 PM on February 4, 2006
why not just format the cells in the birthdate column as Custom -> m/d?
then select all your columns of data, right click and choose "Create List." a column menu will pop up over each column. select the menu above your birthday column and choose "sort ascending" or "sort descending."
one of my minor tasks at work is to send out birthday cards to employees, and i got tired of scanning an entire spreadsheet of 50+ employees for birthdays in the month of X. this was my stop-gap solution.
posted by littlegirlblue at 1:40 PM on February 4, 2006
then select all your columns of data, right click and choose "Create List." a column menu will pop up over each column. select the menu above your birthday column and choose "sort ascending" or "sort descending."
one of my minor tasks at work is to send out birthday cards to employees, and i got tired of scanning an entire spreadsheet of 50+ employees for birthdays in the month of X. this was my stop-gap solution.
posted by littlegirlblue at 1:40 PM on February 4, 2006
You can't use a string function (a la Mutant) on a date. Excel stores dates as a string of numbers that started in 1904 and has been advancing by one each day since. (For example, today, Feb 4th, is 38792).
The =month(a2) solution is the best. You'll get a number between 1 & 12, then you can sort on that.
posted by crapples at 3:39 PM on February 4, 2006
The =month(a2) solution is the best. You'll get a number between 1 & 12, then you can sort on that.
posted by crapples at 3:39 PM on February 4, 2006
I heartily concut with jasper411 and crapples. And if you want to get fancy, you could hide your new column after sorting on it.
posted by JeffK at 3:49 PM on February 4, 2006
posted by JeffK at 3:49 PM on February 4, 2006
I'd make it really simple and just add a column with a number for each month. K.I.S.S.
posted by kdern at 8:46 PM on February 4, 2006
posted by kdern at 8:46 PM on February 4, 2006
This thread is closed to new comments.
posted by Mutant at 11:29 AM on February 4, 2006