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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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...

Row\Col
J​
K​
L​
M​
N​
O​
382​
2015​
383​
MonthDay
384​
NamesBirthday
3​
1​
3/1/2015​
385​
7/8/2015​
Joe
7/8/1965​
6​
8​
6/8/2015​
386​
5/7/2015​
Harry
5/7/1980​
Count
5​
387​
Anniversary Sublist
388​
John
3/1​
389​
Robin
3/9​
390​
7/8/2015​
George
7/8/1965​
Harry
5/7​
391​
6/8/2015​
Rick
6/8/1982​
Rick
6/8​
392​
3/9/2015​
Robin
3/9/1960​
Ringo
6/8​
393​
3/1/2015​
John
3/1/1968​
394​
7/8/2015​
Paul
7/8/1987​
395​
6/8/2015​
Ringo
6/8/1990​
396​

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

S
  • Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
95
bebo021999
B
C
  • Locked
  • Question
Percent Complete Versus Start and End Date Formulas For Conditional Formatting
2 3
Replies
23
Views
1K
Consult123
C
J
  • Question
Grouping data to M-F workweeks.
Replies
1
Views
227
LydMix
L
A
  • Question
How to generate list of start dates
Replies
1
Views
155
JamesCanale
JamesCanale
S
  • Solved
Formula Help Date Arrays
Replies
3
Views
362
Fluff
Fluff

Forum statistics

Threads
1,215,338
Messages
6,124,358
Members
449,155
Latest member
ravioli44

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