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.

 

[Phil Smith]

Hi Fluff. That works until the first correctly adjusted payday on a Thursdsay but it then doesn't revert to the normal 4 weekly Friday payday.

 

[Fluff]

What dates would you expect?

 

[steve the fish]

Will the first date populated always be a friday?

 

[Phil Smith]

CA_Punit: I couldn't work out your cell references to the partial picture of your worksheet, nor in relation to my column A waiting to be populated with dates from my named range "Bank_Holidays"

 

[Phil Smith]

Hi Fluff and steve the fish.

My paydays ALWAYS fall on every 4th Friday except if there is a bank holiday on the Friday. If it is then brought forward by one day at Easter or one/two days at Christmas, it reverts to four weeks after that Friday bank holiday when it would normally have been paid.

 

[steve the fish]

Its the first date i was talking about. That crucially has to be a friday for this to work:

=WORKDAY($A$2+1+ROWS($A$1:A1)*28,-1,Bank_Holidays)

 

[Fluff]

@steve the fish
That gives the same results as my solution, which the OP has said is wrong.

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

Cell Formulas
Range	Formula
A3:A15	A3	=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,F2:F6)
B3:B15	B3	=WORKDAY($B$2+1+ROWS($B$1:B1)*28,-1,$F$2:$F$6)
Dynamic array formulas.

Which is why I asked for a list of expected dates

 

[steve the fish]

Hi Fluff

From what has been asked for it looks fine to me. All fridays 28 days apart excepting the holiday days. I just added that because not everyone has the new functions.

 

[Fluff]

I agree, just waiting on the OP.

 

[Phil Smith]

Sorry guys. Whilst I was working on it some more, I missed your responses. The solution I finally came up with was this:

=IF(WEEKDAY(A2,2)=3,A2+30,IF(WEEKDAY(A2,2)=4,A2+29,IF(AND(WEEKDAY(A2,2)=5,COUNTIF(Bank_Holidays,A2+28),COUNTIF(Bank_Holidays,A2+27)),A2+26,IF(AND(WEEKDAY(A2,2)=5,COUNTIF(Bank_Holidays,A2+28),NOT(COUNTIF(Bank_Holidays,A2+27))),A2+27,A2+28))))

Explanation:
IF(WEEKDAY(A2,2)=3,A2+30
If the previous payday was a Wednesday, the next payday will be in 30 days time otherwise...

IF(WEEKDAY(A2,2)=4,A2+29
If the previous payday was a Thirsday, the next payday will be in 29 days time otherwise...

IF(AND(WEEKDAY(A2,2)=5,COUNTIF(Bank_Holidays,A2+28),COUNTIF(Bank_Holidays,A2+27)),A2+26
If the previous payday was a Friday AND the Friday AND Thursday in four weeks are bank holidays, then the next payday is on the Wednesday of that week.

IF(AND(WEEKDAY(A2,2)=5,COUNTIF(Bank_Holidays,A2+28),NOT(COUNTIF(Bank_Holidays,A2+27))),A2+27,A2+28))))
If the previous payday was a Friday AND the Friday but NOT the Thursday in four weeks is a bank holiday, the next pay day is on the Thursday otherwise it is on the Friday as usual.

I haven't yet tried your new suggestions but I will do now this reply has been posted.