Array Formula to return list of birthdays falling between a start and end date

R

RicardoCubed

Board Regular
Joined
Jul 10, 2013
Messages
206
Office Version
  1. 365
Platform
  1. Windows
Help appreciated with adjusting the below formula to include returning all birthday between a start and end date. the below works for the intended purpose of determining who has a birthday on the month and day indicated. I want to have the added functionality of adding for example all those who have a birthday a start date and end date.
Thanks for any help out there.


Excel 2010
KLMN
383MonthDay
384NamesBirthday78
385Joe7/8/1965
386Harry5/7/1980
387Will Populate below range with who has birthday
388Joe
389George
390George7/8/1965Paul
391Rick6/8/1982--
392John3/1/1968--
393Paul7/8/1987--
394Ringo6/8/1990--
BDay
Cell Formulas
RangeFormula
M388{=IFERROR(INDEX($K$385:$K$394,SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)=$M$384,IF(DAY($L$385:$L$394)=$N$384,ROW($L$385:$L$394)-ROW($L$385)+1))),ROWS($L$385:L385))),"--")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Sort by date Sort by votes
Hi Aladin. to stick with the same data set say
start date = Month = 5 Day = 7
end date = Month = 6 Day = 8

so list would then have harry, rick and ringo.

Thanks!!!
 
Upvote 0
the ultimate would be to be able to enter any of the month day year criteria in either start and/or end dates and get the filtered list, see below for illustration.


Excel 2010
ABCDEF
2CriteriaMonthDayYear
3NamesBirthdayStart911954
4Joe7/8/1965End681982
5Harry5/7/1980
6Adam8/1/1982Will Populate below range with who has birthday
7Lauren9/1/1954Lauren
8Saul10/22/1960--
9George7/8/1965--
10Rick6/8/1982--
11John3/1/1968--
12Paul7/8/1987--
13Ringo6/8/1990--
Sheet1
Cell Formulas
RangeFormula
C7{=IFERROR(INDEX($A$4:$A$13,SMALL(IF(ISNUMBER($B$4:$B$13),IF(MONTH($B$4:$B$13)=$D$3,IF(DAY($B$4:$B$13)=$E$3,ROW($B$4:$B$13)-ROW($B$4)+1))),ROWS($B$4:B4))),"--")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
RicardoCubed said:
Hi Aladin. to stick with the same data set say
start date = Month = 5 Day = 7
end date = Month = 6 Day = 8

so list would then have harry, rick and ringo.

Thanks!!!
Click to expand...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][/tr]
[tr][td]
382​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
383​
[/td][td][/td][td][/td][td]Month[/td][td]Day[/td][/tr]

[tr][td]
384​
[/td][td]Names[/td][td]Birthday[/td][td]
5​
[/td][td]
7​
[/td][/tr]

[tr][td]
385​
[/td][td]Joe[/td][td]
7/8/1965​
[/td][td]
6​
[/td][td]
8​
[/td][/tr]

[tr][td]
386​
[/td][td]Harry[/td][td]
5/7/1980​
[/td][td]Count[/td][td]
3​
[/td][/tr]

[tr][td]
387​
[/td][td][/td][td][/td][td]Anniversary Sublist[/td][td][/td][/tr]

[tr][td]
388​
[/td][td][/td][td][/td][td]Harry[/td][td]
5/7/1980​
[/td][/tr]

[tr][td]
389​
[/td][td][/td][td][/td][td]Rick[/td][td]
6/8/1982​
[/td][/tr]

[tr][td]
390​
[/td][td]George[/td][td]
7/8/1965​
[/td][td]Ringo[/td][td]
6/8/1990​
[/td][/tr]

[tr][td]
391​
[/td][td]Rick[/td][td]
6/8/1982​
[/td][td][/td][td][/td][/tr]

[tr][td]
392​
[/td][td]John[/td][td]
3/1/1968​
[/td][td][/td][td][/td][/tr]

[tr][td]
393​
[/td][td]Paul[/td][td]
7/8/1987​
[/td][td][/td][td][/td][/tr]

[tr][td]
394​
[/td][td]Ringo[/td][td]
6/8/1990​
[/td][td][/td][td][/td][/tr]

[tr][td]
395​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

N386, control+shift+enter (cse), not just enter:
Rich (BB code): 

=SUM(IF(ISNUMBER(L385:L394),IF(MONTH(L385:L394)>=M384,
    IF(DAY(L385:L394)>=N384,IF(MONTH(L385:L394)<=M385,
    IF(DAY(L385:L394)<=N385,1))))))

M388, cse and copy down:
Rich (BB code): 

=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$394,
   SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=$M$384,
   IF(DAY($L$385:$L$394)>=$N$384,IF(MONTH($L$385:$L$394)<=$M$385,
   IF(DAY($L$385:$L$394)<=$N$385,ROW($K$385:$K$394)-ROW($K$385)+1))))),
   ROWS($M$388:M388))),"")

N388, just enter and copy down:
Rich (BB code): 

=IF($M388="","",VLOOKUP(M388,$K$385:$L$394,2,0))
 
Upvote 0
Aladin. Thank you. Just go back to this and works flawlessly!!!!
Appreciate it.
 
Upvote 0
Well - perhaps I messed it up because I was trying to query larger database and results wrong. I went back into the same, cut and pasted below. When I added Robin with date of 03-09-60 its not counting it or pulling in sublist. Yet when I added Tom it did. Suggestions???


Excel 2010
KLMN
383MonthDay
384NamesBirthday31
385Joe7/8/196568
386Harry5/7/1980Count6
387Aladin5/8/1970Anniversary Sublist
388Robin3/9/1960Harry5/7/1980
389tom3/4/1970Aladin5/8/1970
390George7/8/1965tom3/4/1970
391Rick6/8/1982Rick6/8/1982
392John3/1/1968John3/1/1968
393Paul7/8/1987Ringo6/8/1990
394Ringo6/8/1990
BDay
Cell Formulas
RangeFormula
N388=IF($M388="","",VLOOKUP(M388,K385:L394,2,0))
N386{=SUM(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=M384,IF(DAY($L$385:$L$394)>=N384,IF(MONTH($L$385:$L$394)<=M385,IF(DAY($L$385:$L$394)<=N385,1))))))}
M388{=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$394,SMALL(IF(ISNUMBER($L$385:$L$394),IF(MONTH($L$385:$L$394)>=$M$384,IF(DAY($L$385:$L$394)>=$N$384,IF(MONTH($L$385:$L$394)<=$M$385,IF(DAY($L$385:$L$394)<=$N$385,ROW($K$385:$K$394)-ROW($K$385)+1))))),ROWS($M$388:M388))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
That Robin case is a good counterexample. Thanks for the test.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][/tr]
[tr][td]
382​
[/td][td][/td][td][/td][td]
2015​
[/td][td][/td][/tr]

[tr][td]
383​
[/td][td][/td][td][/td][td]Month[/td][td]Day[/td][/tr]

[tr][td]
384​
[/td][td]Names[/td][td]Birthday[/td][td]
3​
[/td][td]
1​
[/td][/tr]

[tr][td]
385​
[/td][td]Joe[/td][td]
7/8/1965​
[/td][td]
6​
[/td][td]
8​
[/td][/tr]

[tr][td]
386​
[/td][td]Harry[/td][td]
5/7/1980​
[/td][td]Count[/td][td]
5​
[/td][/tr]

[tr][td]
387​
[/td][td][/td][td][/td][td]Anniversary Sublist[/td][td][/td][/tr]

[tr][td]
388​
[/td][td][/td][td][/td][td]Harry[/td][td]
5/7/1980​
[/td][/tr]

[tr][td]
389​
[/td][td][/td][td][/td][td]Rick[/td][td]
6/8/1982​
[/td][/tr]

[tr][td]
390​
[/td][td]George[/td][td]
7/8/1965​
[/td][td]Robin[/td][td]
3/9/1960​
[/td][/tr]

[tr][td]
391​
[/td][td]Rick[/td][td]
6/8/1982​
[/td][td]John[/td][td]
3/1/1968​
[/td][/tr]

[tr][td]
392​
[/td][td]Robin[/td][td]
3/9/1960​
[/td][td]Ringo[/td][td]
6/8/1990​
[/td][/tr]

[tr][td]
393​
[/td][td]John[/td][td]
3/1/1968​
[/td][td][/td][td][/td][/tr]

[tr][td]
394​
[/td][td]Paul[/td][td]
7/8/1987​
[/td][td][/td][td][/td][/tr]

[tr][td]
395​
[/td][td]Ringo[/td][td]
6/8/1990​
[/td][td][/td][td][/td][/tr]

[tr][td]
396​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

M382, just enter:
Rich (BB code): 

=YEAR(TODAY())

N386, control+shift+enter (cse), not just enter:
Rich (BB code): 

=SUM(IF(ISNUMBER($L$385:$L$395),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))>=DATE($M$382,$M$384,$N$384),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))<=DATE($M$382,$M$385,$N$385),
    1))))

M388, cse and copy down:
Rich (BB code): 

=IF(ROWS($M$388:M388)<=$N$386,INDEX($K$385:$K$395,
    SMALL(IF(ISNUMBER($L$385:$L$395),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))>=DATE($M$382,$M$384,$N$384),
    IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))<=DATE($M$382,$M$385,$N$385),
    ROW($K$385:$K$395)-ROW($K$385)+1))),ROWS($M$388:M388))),"")

N388, just enter and copy down:
Rich (BB code): 

=IF($M388="","",VLOOKUP(M388,$K$385:$L$395,2,0))

See the link to the workbook that implements the last version:
https://dl.dropboxusercontent.com/u...ays falling between a start and end date.xlsx
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Number of daytime hours falling between two date ranges
Replies
8
Views
192
Cubist
Cubist
C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
501
chris_bosten
C
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
396
etaf
etaf
S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
372
bebo021999
B
H
  • Solved
Filter function returning zero rows. Assigning results to an array.
Replies
4
Views
149
RoryA
RoryA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top