Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
I have a long list of names with birthdates. When I sort from A to Z it puts
them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
Lynn
I think you should either use a pivot table and look at the Grouping feature (you can group data by month for example), or use a helper column to identify what month the birthday is in, so that you can use that to filter on. An example of such a helper would be: (Assumes birthdates in A col) =TEXT(A1,"mmmm") which will return January,February etc. You can then filter on this column (using Autofilter). Does this help? Richard Lynn wrote: I have a long list of names with birthdates. When I sort from A to Z it puts them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
You have several options....
Here's one: With A5:A100 contains names, with A1: Name B5:B100 contains birthdates, with B1: Birthdate Then..try this A1: Jan B1: MonthTest (or Blank or any other text that is NOT a column heading) B2: =TEXT(B6,"mmm")=$A$1 Select A5:B100 Then....From the Excel main menu: <data<filter<advanced filter List Range: $A$5:$B$100 Criteria Range: $B$1:$B$2 Click the [OK] button Only names with Birthdays in January will be visible. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Lynn" wrote: I have a long list of names with birthdates. When I sort from A to Z it puts them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
Hi
The easiest way - add a column where you calculate the month from birthdate =MONTH(Birthdate) , or =TEXT(Birthdate,"mmmm") (replace Birthdate with cell reference) , and the use autofilter to display only records for desired month. Sorting your data isn't obligatory, unless you want filtered records to be displayed in date order. Arvi Laanemets "Lynn" wrote in message ... I have a long list of names with birthdates. When I sort from A to Z it puts them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
Assuming Column B contain birthday dates
Create a helper column in C C2 =MONTH(B2) copy down as far as needed Sort on column C "Lynn" wrote: I have a long list of names with birthdates. When I sort from A to Z it puts them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find birthdays for January from a list
Ummmm...typo
The column headings should be A5: Name B5: Birthdate (Instead of A1 and B1, respectively) By way of atoning for that.... Here's another option, using Conditional Formatting Using my posted example: Select A6:A100 <format<conditional formatting Formula is: =TEXT(B6,"mmm")=$A$1 Click the [format] button and set a bright bacground color Clidk the [OK] buttons and you're done Now...all names with a birthdate in the month entered in A1 will highlight. Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: You have several options.... Here's one: With A5:A100 contains names, with A1: Name B5:B100 contains birthdates, with B1: Birthdate Then..try this A1: Jan B1: MonthTest (or Blank or any other text that is NOT a column heading) B2: =TEXT(B6,"mmm")=$A$1 Select A5:B100 Then....From the Excel main menu: <data<filter<advanced filter List Range: $A$5:$B$100 Criteria Range: $B$1:$B$2 Click the [OK] button Only names with Birthdays in January will be visible. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Lynn" wrote: I have a long list of names with birthdates. When I sort from A to Z it puts them in ascending date order. How can I find just the birthdates in a given month, regardless of year? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and delete Both Dupblicate Cells in a list | New Users to Excel | |||
Subtotals in a list | Excel Discussion (Misc queries) | |||
formula to find the data of a next cell in a list | Excel Discussion (Misc queries) | |||
Find name in list and get dept # enter dept # on work sheet | Excel Worksheet Functions | |||
Find in list function | New Users to Excel |