Does my payday fall on a Bank Holiday?

[Phil Smith]

Hi People of the Forum.

1) I wish to create a list of my paydays which occur every 4 weeks on a Friday. Easy enough. However...

2) I am scratching my head trying to come up with a workable formula to take into account whether a payday falls on a Bank Holiday, and if it does, have the payday fall one day early on the Thursday. However...

3) Should Payday Friday happen to fall on December 26th, it would firstly get moved to the 25th, which then means it would then have to be moved to Wednesday 24th December.

The column containing the list of paydays will start in A2. I also have a named range, "Bank_Holidays", which, surprisingly, contains the required bank holiday dates.?

Should a Excel wizard come up with a solution in VBA that is more than acceptable also.

Thanks in advance.

 

[Fluff]

All 3 formulae give the same result

+Fluff.xlsm
	A	B	C	D	E	F
1
2	27/11/2015	27/11/2015	27/11/2015			24/12/2015
3	23/12/2015	23/12/2015	23/12/2015			25/12/2015
4	22/01/2016	22/01/2016	22/01/2016			26/12/2015
5	19/02/2016	19/02/2016	19/02/2016			01/01/2016
6	17/03/2016	17/03/2016	17/03/2016			18/03/2016
7	15/04/2016	15/04/2016	15/04/2016
8	13/05/2016	13/05/2016	13/05/2016
9	10/06/2016	10/06/2016	10/06/2016
10	08/07/2016	08/07/2016	08/07/2016
11	05/08/2016	05/08/2016	05/08/2016
12	02/09/2016	02/09/2016	02/09/2016
13	30/09/2016	30/09/2016	30/09/2016
14	28/10/2016	28/10/2016	28/10/2016
15	25/11/2016	25/11/2016	25/11/2016
16
List

Cell Formulas
Range	Formula
A3:A15	A3	=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,Bank_Holidays)
B3:B15	B3	=WORKDAY($B$2+1+ROWS($B$1:B1)*28,-1,Bank_Holidays)
C3:C15	C3	=IF(WEEKDAY(C2,2)=3,C2+30,IF(WEEKDAY(C2,2)=4,C2+29,IF(AND(WEEKDAY(C2,2)=5,COUNTIF(Bank_Holidays,C2+28),COUNTIF(Bank_Holidays,C2+27)),C2+26,IF(AND(WEEKDAY(C2,2)=5,COUNTIF(Bank_Holidays,C2+28),NOT(COUNTIF(Bank_Holidays,C2+27))),C2+27,C2+28))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Bank_Holidays	=List!$F$2:$F$6	A3, B3:C15

 

[Phil Smith]

Thank you! Both your solutions did work. Fluff, I must have done something wrong when I tried first of all. Apologies!
I dont have a clue as to how your formulas work, but they are both more concise than mine!
Thank you once again!

 

[Fluff]

The formula in col B above is not mine but from @steve the fish

Glad we could help & thanks for the feedback