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
Thanks Aladin: Was able to SUCCESSFULLY replicate in both the example posted and my larger database!!!!
WOW!!! Really great and I have other applications to use this same functionality with!!!!!!!!

Sensei: Curious. Is there a way to formulate that the sublist would fill by earliest date to latest date?
Not a must have - but would be nice if the sublist went in chronological order.
I know I can sort manually - which is fine - but curious if can be constructed into a formula?

Regards,
Grasshopper - aka RicardoCubed
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
RicardoCubed said:
Thanks Aladin: Was able to SUCCESSFULLY replicate in both the example posted and my larger database!!!!
WOW!!! Really great and I have other applications to use this same functionality with!!!!!!!!
Click to expand...

Good news!..

Sensei: Curious. Is there a way to formulate that the sublist would fill by earliest date to latest date?
Not a must have - but would be nice if the sublist went in chronological order.
I know I can sort manually - which is fine - but curious if can be constructed into a formula?

Regards,
Grasshopper - aka RicardoCubed
Click to expand...

M382:

=YEAR(TODAY())

N386, control+shift+enter:

=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))))

The foregoing formulas are the same as before, unmodified.

M388, control+shift+enter and copy down:

=IF($N388="","",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),IF(DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))=$N388,ROW($K$385:$K$395)-ROW($K$385)+1)))),COUNTIF($N$388:N388,N388))))

N388, control+shift+enter, custom format as m/d;@, and copy down:

=IF(ROWS($M$388:M388)<=$N$386,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),DATE($M$382,MONTH($L$385:$L$395),DAY($L$385:$L$395))))),ROWS($M$388:M388)),"")
 
Upvote 0
TRULY A WORK OF A MASTER! REALLY AWESOME!
Works perfectly on the smaller sample and now just translating to my larger sheet.
THANKS SO MUCH!!!!!!
 
Upvote 0
Aladin: Hi again and I am really sorry to bother you on this for I think its just me not "seeing". I am sure I am missing something obvious but I have tried and tried.
Your solution works flawlessly on the sample but when I go to make it work with my real data its not working.
I am sure I have a comma, or ( ) wrong but I have tried and tried (three times). Can you see what the problem is?
Thanks and I'll look again but think I need to walk away for awhile.


Excel 2010
BCDEFG
3982015
399MonthDay
400Start212
401End216
402Count4
403Anniversary SublistToday
404CountName - ConcatenatedM/D BirthYearAgeCorrect DoB
4051Joseph - Property, Marnell02/12198332.0602/16/83
4062Pierre-Louis, Claude02/12195857.0702/12/58
4073Fitzgerald, LaQueen02/12197936.0702/12/79
4084Atanacio, Phillipe02/16198728.0702/12/87
BDay
Cell Formulas
RangeFormula
C398=YEAR(TODAY())
C405{=IF(ROWS($C$405:C405)<=$D$402,INDEX(Detail!$CT$3:$CT$590,SMALL(IF(ISNUMBER(Detail!$CH$3:$CH$590),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$590),DAY(Detail!$CH$3:$CH$590))>=DATE($C$398,$C$400,$D$400),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$590),DAY(Detail!$CH$3:$CH$590))<=DATE($C$398,$C$401,$D$401),ROW(Detail!$CT$3:$CT$590)-ROW(Detail!$CT$3)+1))),ROWS($C$405:C405))),"-")}
B405=IF(D405<>"NA",ROW()-ROW($B$405:B405)+1,"-")
E405=INDEX(Detail!$CH$3:$CH$590,MATCH(C405,Detail!$CT$3:$CT$590,0))
F405=YEARFRAC(E405,TODAY(),1)
G405=INDEX(Detail!$CH$3:$CH$531,MATCH(C405,Detail!$CT$3:$CT$531,0))
D402{=SUM(IF(ISNUMBER(Detail!$CH$3:$CH$531),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$531),DAY(Detail!$CH$3:$CH$531))>=DATE($C$398,$C$400,$D$400),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$531),DAY(Detail!$CH$3:$CH$531))<=DATE($C$398,$C$401,$D$401),1))))}
D405{=IF(ROWS($C$405:C405)<=$D$402,SMALL(IF(ISNUMBER(Detail!$CH$3:$CH$531),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$531),DAY(Detail!$CH$3:$CH$531))>=DATE($C$398,$C$400,$D$400),IF(DATE($C$398,MONTH(Detail!$CH$3:$CH$531),DAY(Detail!$CH$3:$CH$531))<=DATE($C$398,$C$401,$D$401),DATE($C$398,MONTH(Detail!$CH$3:$CH$531),DAY(Detail!$CH$3:$CH$531))))),ROWS($C$405:C405)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
RicardoCubed said:
Aladin: Hi again and I am really sorry to bother you on this for I think its just me not "seeing". I am sure I am missing something obvious but I have tried and tried.
Your solution works flawlessly on the sample but when I go to make it work with my real data its not working.
I am sure I have a comma, or ( ) wrong but I have tried and tried (three times). Can you see what the problem is?
Thanks and I'll look again but think I need to walk away for awhile.
[...]
Click to expand...

Let's take a less complicated route for maintaining/adapting lengthy formulas can be a real burden...

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

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

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

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

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

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

[tr][td]
388​
[/td][td][/td][td][/td][td][/td][td]John[/td][td]
3/1​
[/td][td][/td][/tr]

[tr][td]
389​
[/td][td][/td][td][/td][td][/td][td]Robin[/td][td]
3/9​
[/td][td][/td][/tr]

[tr][td]
390​
[/td][td]
7/8/2015​
[/td][td]George[/td][td]
7/8/1965​
[/td][td]Harry[/td][td]
5/7​
[/td][td][/td][/tr]

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

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

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

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

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

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

O384, copied to O385...

=DATE($M$382,M384,N384)

J385, copied down:

=IF(ISNUMBER(L385),DATE($M$382,MONTH(L385),DAY(L385)),"")

N386, cse...

=SUM(IF(ISNUMBER($L$385:$L$395),IF($J$385:$J$395>=$O$384,IF($J$385:$J$395<=$O$385,1))))

M388, cse and copy down...

=IF($N388="","",INDEX($K$385:$K$395,SMALL(IF(ISNUMBER($L$385:$L$395),IF($J$385:$J$395>=$O$384,IF($L$385:$L$395<=$O$385,IF($J$385:$J$395=$N388,ROW($K$385:$K$395)-ROW($K$385)+1)))),COUNTIF($N$388:N388,N388))))

N388, cse and copy down...

=IF(ROWS($M$388:M388)<=$N$386,SMALL(IF(ISNUMBER($L$385:$L$395),IF($J$385:$J$395>=$O$384,IF($J$385:$J$395<=$O$385,$J$385:$J$395))),ROWS($M$388:M388)),"")

See the implementation in a workbook: https://dl.dropboxusercontent.com/u...a start and end date ascending-version-2.xlsx
 
Upvote 0
Thank you again!!! Will work on and advise if I am able to implement in my sheet.
Worked great in an expanded sample but had issue with first cut on my workbook - but plodding thru it. Sincere appreciation to all the hard work you put into it.
If ever USA way and in NYC area would like to show appreciation w/nice dinner!
Will advise how I make out.
Regards,
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
337
chris_bosten
C
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
342
bebo021999
B
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
137
Ludwig
L
C
  • Locked
  • Question Question
Percent Complete Versus Start and End Date Formulas For Conditional Formatting
2 3
Replies
23
Views
2K
Consult123
C

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

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