Formula to Find Birthday matching date in a cell

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
100
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Good Morning

Please could I get some help with a formula to find birthday's from a list that match a date. To be more specific

Tab "Staff" A2:B24" is the table that has column "A" Birthday Date" in Format DD/MM and Column B has the staff members name
Tab Sheet 1 has a roster in where B:3 to G:4 has Monday to Sunday above and the date in format DD/MM/YYYY. In cell B3 I want the formula to look at the date in B1 and match it to the date in Staff Tab and return the name of who's birthday it is, if applicable. But the formula will need to know to match just the DD MM not the year, as otherwise it won't match.

Not sure if I'm making any sense?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
With Excel 365 see example in column B, with Excel 2016 see column D.

Sheet1
Book1
ABCD
13/8/2021
2
3Staff11Staff11
4Staff14Staff14
Sheet1
Cell Formulas
RangeFormula
B3:B4B3=FILTER(Staff!$B$2:$B$24,(MONTH(Staff!$A$2:$A$24)=MONTH($B$1))*(DAY(Staff!$A$2:$A$24)=DAY($B$1)),"No Birthdays")
D3:D4D3=IFERROR(INDEX(Staff!$B$2:$B$24,AGGREGATE(15,6,(ROW(Staff!$B$2:$B$24)-ROW(Staff!$B$2)+1)/(MONTH(Staff!$A$2:$A$24)=MONTH($B$1))*(DAY(Staff!$A$2:$A$24)=DAY($B$1)),ROWS($D$3:D3))),"")
Dynamic array formulas.


Staff
Book1
AB
1BDStaff
211/19/1963Staff1
310/1/1975Staff2
410/31/1976Staff3
510/5/1969Staff4
65/22/1964Staff5
78/8/1961Staff6
812/1/1969Staff7
95/22/1965Staff8
1010/4/1977Staff9
1110/29/1978Staff10
123/8/1970Staff11
139/16/1967Staff12
1410/5/1970Staff13
153/8/1977Staff14
161/14/1973Staff15
179/8/1981Staff16
182/17/1968Staff17
195/1/1983Staff18
205/8/1987Staff19
216/16/1964Staff20
221/13/1978Staff21
2310/9/1986Staff22
244/1/1971Staff23
Staff
 
Upvote 0
Thanks so much for the quick reply. I tried that but it's not returning any value. I've recreated the sheets in a new book and took out the personal details so you can have a look at what I'm maybe doing wrong, but Monday should show Staff 23 as a name.

Book1 birthday test.xlsx
ABCDEFG
120218/03/20219/03/202110/03/202111/03/202112/03/202113/03/2021
2NAMEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Staff Birthday's This Week 
4
10
Cell Formulas
RangeFormula
B1B1=DATE($A$1, 1, -2) - WEEKDAY(DATE($A$1, 1, 3)) + $L$1 * 7
C1:G1C1=B1+1
B3B3=IFERROR(INDEX(Staff!$B$2:$B$24,MATCH(B$1,Staff!$A$2:$A$24,0),1),"")
 
Upvote 0
Book1 birthday test.xlsx
ABCDEFGHIJKLMNOPQR
1VLOOKUP BDAYNameRoleVisa/Work EligibilityBirthdayDOBCurrent Age
217/01Staff 1LabourerCitizen17 January17/01/197744
307/02Staff 2LabourerCitizen07 February07/02/196952
422/02Staff 3LabourerCitizen22 February22/02/199625
504/03Staff 4LabourerCitizen04 March04/03/200120
624/03Staff 5LabourerCitizen24 March24/03/200219
726/03Staff 6LabourerCitizen26 March26/03/200219
829/04Staff 7LabourerCitizen29 April29/04/200219
920/05Staff 8LabourerResident20 May20/05/200219
1006/06Staff 9LabourerResident06 June06/06/198734
1111/06Staff 10LabourerCitizen11 June11/06/199724
1212/06Staff 11LabourerCitizen12 June12/06/197645
1305/07Staff 12LabourerResident05 July05/07/198932
1417/07Staff 13LabourerResident17 July17/07/199823
1506/08Staff 14LabourerCitizen06 August06/08/198635
1625/08Staff 15LabourerCitizen25 August25/08/198338
1724/09Staff 16LabourerCitizen24 September24/09/199921
1830/09Staff 17LabourerPartner Visa30 September30/09/197743
1914/10Staff 18LabourerCitizen14 October14/10/200218
2026/10Staff 19LabourerCitizen26 October26/10/199129
2104/11Staff 20LabourerCitizen04 November04/11/199624
2221/11Staff 21LabourerResident21 November21/11/196060
2305/12Staff 22LabourerCitizen05 December05/12/199030
2408/03Staff 23LabourerCitizen08 March08/03/198536
Staff
Cell Formulas
RangeFormula
A2:A24A2=IF(M2="","",TEXT(M2,"DD")&"/"&TEXT(M2,"mm"))
L2:L24L2=IF(M2="","",TEXT(M2,"DD")&" "&TEXT(M2,"mmmm"))
N2:N24N2=IF(L2="","",(TODAY()-M2)/365)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H2:H7Cellcontains an errortextNO
G2:H7Dates Occurringnext weektextNO
P2:Q24Cell Value=0textNO
 
Upvote 0
With Excel 365 see example in column B, with Excel 2016 see column D.

Sheet1
Book1
ABCD
13/8/2021
2
3Staff11Staff11
4Staff14Staff14
Sheet1
Cell Formulas
RangeFormula
B3:B4B3=FILTER(Staff!$B$2:$B$24,(MONTH(Staff!$A$2:$A$24)=MONTH($B$1))*(DAY(Staff!$A$2:$A$24)=DAY($B$1)),"No Birthdays")
D3:D4D3=IFERROR(INDEX(Staff!$B$2:$B$24,AGGREGATE(15,6,(ROW(Staff!$B$2:$B$24)-ROW(Staff!$B$2)+1)/(MONTH(Staff!$A$2:$A$24)=MONTH($B$1))*(DAY(Staff!$A$2:$A$24)=DAY($B$1)),ROWS($D$3:D3))),"")
Dynamic array formulas.


Staff
Book1
AB
1BDStaff
211/19/1963Staff1
310/1/1975Staff2
410/31/1976Staff3
510/5/1969Staff4
65/22/1964Staff5
78/8/1961Staff6
812/1/1969Staff7
95/22/1965Staff8
1010/4/1977Staff9
1110/29/1978Staff10
123/8/1970Staff11
139/16/1967Staff12
1410/5/1970Staff13
153/8/1977Staff14
161/14/1973Staff15
179/8/1981Staff16
182/17/1968Staff17
195/1/1983Staff18
205/8/1987Staff19
216/16/1964Staff20
221/13/1978Staff21
2310/9/1986Staff22
244/1/1971Staff23
Staff
Gave this one a go, and no results come up in the cells
1615415189507.png
 
Upvote 0
With Excel 365. Drag formula accross.
Note my dates are MM/DD/YY

Sheet1
Book2
ABCDEFG
120213/8/20213/9/20213/10/20213/11/20213/12/20213/13/2021
2NAMEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Staff Birthday's This WeekStaff 11No BirthdaysStaff 5Staff 12No BirthdaysNo Birthdays
4Staff 14Staff 18
5Staff 21
Sheet1
Cell Formulas
RangeFormula
C1:G1C1=B1+1
B3:B4,D3:D5,C3,E3:G3B3=FILTER(Staff!$B$2:$B$24,(MONTH(Staff!$A$2:$A$24)=MONTH(B$1))*(DAY(Staff!$A$2:$A$24)=DAY(B$1)),"No Birthdays")
Dynamic array formulas.


Staff
Book2
ABC
1VLOOKUP BDAYNameRole
211/19Staff 1Labourer
310/01Staff 2Labourer
410/31Staff 3Labourer
510/05Staff 4Labourer
603/10Staff 5Labourer
708/08Staff 6Labourer
812/01Staff 7Labourer
905/22Staff 8Labourer
1010/04Staff 9Labourer
1110/29Staff 10Labourer
1203/08Staff 11Labourer
1303/11Staff 12Labourer
1410/05Staff 13Labourer
1503/08Staff 14Labourer
1601/14Staff 15Labourer
1709/08Staff 16Labourer
1802/17Staff 17Labourer
1903/10Staff 18Labourer
2005/08Staff 19Labourer
2106/16Staff 20Labourer
2203/10Staff 21Labourer
2310/09Staff 22Labourer
2404/01Staff 23Labourer
Staff
 
Upvote 0
Thank you, how do I edit the formula for my dates that are DD/MM/YYYY
 
Upvote 0
You shouldn't need to, as long as all your dates are actual dates.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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