FILTER on multiple dates and MATCH by name

L

lbanham

Board Regular
Joined
Feb 17, 2011
Messages
50
Evening

I am creating a rota and i am looking for help write a formula to check if a person in on holiday at specific dates, or is a non working day for them.
I have tried various Index Match combinations, FILTER (i am on 365) and numerous other but can only get it working for a single date, not a range. Column A to C Shows the holidays, E to F Shows non working days and Column L to M is where i want to populate (hard coded in the example to show the outcome).

This is an example of one of the attempts (for a single date not a range) IFERROR(IF(FILTER(Holiday,ISNUMBER(SEARCH(L$2,Holiday))*(B3=K3),"")<>"","Holiday",IF(MATCH(L$2&LEFT($H3,3),$b$3:$b$8,0),"Unavailable")),IF(FILTER(Holiday,ISNUMBER(SEARCH(L$2,Holiday))*(B3=K3),"")<>"","Holiday",""))

any help is appreciated. Thank you

Rota Formula Example.xlsx
ABCDEFGHIJKLMNO
1HolidaysWorking PatternFormula needed to identify when a person is on hoiday or is a non working day
2PersonStartEndPersonNon Working DayDatePersonApprover checkShort DatePerson 1Person 2Person 3
3Person 131/01/202203/02/2022Person 1FriMonday, 31 January 2022Person 1Check31/01/2022Holiday
4Person 202/02/202202/02/2022Person 2ThurTuesday, 01 February 2022Person 201/02/2022Holiday
5Person 314/02/202216/02/2022Person 3NoneWednesday, 02 February 2022Person 302/02/2022HolidayHoliday
6Person 110/02/202210/02/2022Thursday, 03 February 2022Person 303/02/2022HolidayUnavailable
7Person 223/02/202225/02/2022Friday, 04 February 2022Person 1Check04/02/2022Unavailable
8Person 323/02/202223/02/2022Monday, 07 February 2022Person 107/02/2022
9Tuesday, 08 February 2022Person 208/02/2022
10Wednesday, 09 February 2022Person 309/02/2022
11Thursday, 10 February 2022Person 2Check10/02/2022HolidayUnavailable
12Friday, 11 February 202211/02/2022Unavailable
13Monday, 14 February 202214/02/2022Holiday
14Tuesday, 15 February 202215/02/2022Holiday
15Wednesday, 16 February 202216/02/2022Holiday
16Thursday, 17 February 202217/02/2022Unavailable
17Friday, 18 February 202218/02/2022Unavailable
18Monday, 21 February 202221/02/2022
19Tuesday, 22 February 202222/02/2022
20Wednesday, 23 February 202223/02/2022HolidayHoliday
21Thursday, 24 February 202224/02/2022Unavailable
22Friday, 25 February 202225/02/2022UnavailableHoliday
23
Sheet1
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1HolidaysWorking Pattern
2PersonStartEndPersonNon Working DayDatePersonApprover checkShort DatePerson 1Person 2Person 3
3Person 131/01/202203/02/2022Person 1FriMonday, 31 January 2022Person 1Check31/01/2022Holiday  
4Person 202/02/202202/02/2022Person 2ThuTuesday, 01 February 2022Person 201/02/2022Holiday  
5Person 314/02/202216/02/2022Person 3NoneWednesday, 02 February 2022Person 302/02/2022HolidayHoliday 
6Person 110/02/202210/02/2022Thursday, 03 February 2022Person 303/02/2022HolidayUnavailable 
7Person 223/02/202225/02/2022Friday, 04 February 2022Person 1Check04/02/2022Unavailable  
8Person 323/02/202223/02/2022Monday, 07 February 2022Person 107/02/2022   
9Tuesday, 08 February 2022Person 208/02/2022   
10Wednesday, 09 February 2022Person 309/02/2022   
11Thursday, 10 February 2022Person 2Check10/02/2022HolidayUnavailable 
12Friday, 11 February 202211/02/2022Unavailable  
13Monday, 14 February 202214/02/2022  Holiday
14Tuesday, 15 February 202215/02/2022  Holiday
15Wednesday, 16 February 202216/02/2022  Holiday
16Thursday, 17 February 202217/02/2022 Unavailable 
17Friday, 18 February 202218/02/2022Unavailable  
18Monday, 21 February 202221/02/2022   
19Tuesday, 22 February 202222/02/2022   
20Wednesday, 23 February 202223/02/2022 HolidayHoliday
21Thursday, 24 February 202224/02/2022 Unavailable 
22Friday, 25 February 202225/02/2022UnavailableHoliday 
Summary
Cell Formulas
RangeFormula
L3:N22L3=IF(INDEX($F$3:$F$10,MATCH(L$2,$E$3:$E$10,0))=TEXT($K3,"ddd"),"Unavailable",IF(COUNTIFS($A:$A,L$2,$B:$B,"<="&$K3,$C:$C,">="&$K3),"Holiday",""))
 
Upvote 0
Solution
Fluff said:
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1HolidaysWorking Pattern
2PersonStartEndPersonNon Working DayDatePersonApprover checkShort DatePerson 1Person 2Person 3
3Person 131/01/202203/02/2022Person 1FriMonday, 31 January 2022Person 1Check31/01/2022Holiday  
4Person 202/02/202202/02/2022Person 2ThuTuesday, 01 February 2022Person 201/02/2022Holiday  
5Person 314/02/202216/02/2022Person 3NoneWednesday, 02 February 2022Person 302/02/2022HolidayHoliday 
6Person 110/02/202210/02/2022Thursday, 03 February 2022Person 303/02/2022HolidayUnavailable 
7Person 223/02/202225/02/2022Friday, 04 February 2022Person 1Check04/02/2022Unavailable  
8Person 323/02/202223/02/2022Monday, 07 February 2022Person 107/02/2022   
9Tuesday, 08 February 2022Person 208/02/2022   
10Wednesday, 09 February 2022Person 309/02/2022   
11Thursday, 10 February 2022Person 2Check10/02/2022HolidayUnavailable 
12Friday, 11 February 202211/02/2022Unavailable  
13Monday, 14 February 202214/02/2022  Holiday
14Tuesday, 15 February 202215/02/2022  Holiday
15Wednesday, 16 February 202216/02/2022  Holiday
16Thursday, 17 February 202217/02/2022 Unavailable 
17Friday, 18 February 202218/02/2022Unavailable  
18Monday, 21 February 202221/02/2022   
19Tuesday, 22 February 202222/02/2022   
20Wednesday, 23 February 202223/02/2022 HolidayHoliday
21Thursday, 24 February 202224/02/2022 Unavailable 
22Friday, 25 February 202225/02/2022UnavailableHoliday 
Summary
Cell Formulas
RangeFormula
L3:N22L3=IF(INDEX($F$3:$F$10,MATCH(L$2,$E$3:$E$10,0))=TEXT($K3,"ddd"),"Unavailable",IF(COUNTIFS($A:$A,L$2,$B:$B,"<="&$K3,$C:$C,">="&$K3),"Holiday",""))
Click to expand...
Thank you this worked perfectly!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Displaying a value from a specific data range when using the Match function
Replies
0
Views
305
Cris4py
C
ExcelNewbie2020
  • Solved
filter, sort and remove extra name
Replies
9
Views
455
ExcelNewbie2020
ExcelNewbie2020
K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
241
Eric W
Eric W
H
  • Question
Fuzzy Search/Filter 2 columns including date
Replies
0
Views
407
High77
H
C
  • Solved
Index Match across worksheets with criteria
Replies
5
Views
417
Fluff
Fluff

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

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