Filter by month, not year in Excel?

February 4, 2006 11:21 AM

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?

posted by ggypsy to computers & internet (9 comments total)

I'd insert three columns that are used to hold day / month / year respectively. You'll have to use Excel's built in string functions to splite the birthdate field into three columns, but once you've done that you can pretty easily 1) sort on month as you require, and 2) hide the extra three columns for reporting purposes.
posted by Mutant at 11:29 AM on February 4, 2006

Using Excel's PivotTable thingy should also allow you to accomplish this.
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]

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

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

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

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

concur, even.
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