Formula to Find Birthday matching date in a cell

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
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?
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
For some reason I'm not getting a result in my sheet? B3 should show staff 23?

Book1 birthday test.xlsx
ABCDEFG
1202103/08/2103/09/2103/10/2103/11/2103/12/2103/13/21
2NAMEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Staff Birthday's This WeekStaff 23
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=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")
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
Lets check to see what month and day is being returned on sheet1 and Staff sheet.
In one cell enter =MONTH(B1) and in another enter DAY(B1) to see if it returns the correct month and day.
Then do the same for your STAFF tab for staff 23 and see if the month and day are the same as on sheet1.
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Could it maybe be because my date is a formula in B1?
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yup they are returning the correct values (you'll see in B7:C8)
Book1 birthday test.xlsx
ABCDEFG
1202103/08/2103/09/2103/10/2103/11/2103/12/2103/13/21
2NAMEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Staff Birthday's This WeekStaff 23
4
5
6
733
888
10
Cell Formulas
RangeFormula
B1B1=DATE($A$1, 1, -2) - WEEKDAY(DATE($A$1, 1, 3)) + $L$1 * 7
C1:G1C1=B1+1
B3B3=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")
B7B7=MONTH(B1)
C7C7=MONTH(Staff!A24)
B8B8=DAY(B1)
C8C8=DAY(Staff!A24)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
C7:F7Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B6Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B5Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
B8Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
C6:F6Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
E8Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
C5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D5Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
D8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
F8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
C8Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
E5:F5Cellcontains a blank value textNO
E5:F5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
E5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
F5Cellcontains a blank value textNO
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What does this return?
It should return an array of 0's and 1's.
Actual in this case it should only return one 1 in the position for Staff 23
=(MONTH(Staff!$A$2:$A$24)=MONTH(B$1))*(DAY(Staff!$A$2:$A$24)=DAY(B$1))
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If I put that formula in B3 it shows blank

1615422905163.png
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Could it be that the text in B3 is formatted as WHITE?
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Oh you are GENIUS, yes thank you, now I'm getting a result. I removed all the conditional formatting but never thought that the font would be white. Thank you so much!!!!!!
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
88
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
One more question, if there are 2 on people on the same day, will it put in both names?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
Yes, in Excel 365 if there are 2 or more people on the same day the FILTER function will spill down (as in my example). Also in 365 there is no need to use CTRL-SHIFT-ENTER.
If you need a formula for 2016 let me know.
 

Forum statistics

Threads
1,141,485
Messages
5,706,654
Members
421,459
Latest member
Taamrak

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
Top