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.

 

[steve the fish]

Try this:

=WORKDAY(A1+1,-1,Bank_Holidays)

 

[Phil Smith]

Thanks, but even correcting it to =WORKDAY(A2+1,-1,Bank_Holidays) that just repeats the same date...??‍

 

[steve the fish]

It supposed to.. unless its a bank holiday

 

[Phil Smith]

Thanks for your help. I will try to re-phrase the question.

I have one date, in A2, which is the first payday in the list and, for example, we will say it is Friday 27th November 2015.

I require a formula that I can populate the cells below A2 that will check against the Bank_Holidays named range to see if the next payday in 28 days falls on a bank holiday.

If it does fall on a bank holiday, I will then need to check to see if the Thursday is also a bank holiday, in which case payday will be on the Wednesday. Using the date above, 4 weeks later would see payday fall on Boxing Day. Moving it one day earlier would have it falling on Christmas Day necessitating payday to therefore fall on Wednesday 24th December.

If payday has been moved to a Thursday, the next date to check would be in 29 days and if it had been moved to a Wednesday, as in the example above, the next date to be checked would be in 30 days.

The vast majority of paydays would be from Friday to 4 weeks Friday. It is, usually, only Easter or Christmas that fouls things up.

I hope that has made it clearer.

 

[CA_Punit]

=SMALL(IF(K4-1=$N$3:$N$7,K4-2,K4-1),1)

 

[CA_Punit]

Hope i have correctly computed the data...
If there are two holidays, i.e. wednessday and thursday at the same time this will not compute correctly.

 

[Fluff]

Maybe

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

Cell Formulas
Range	Formula
A3:A15	A3	=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,F3:F6)
Dynamic array formulas.

 

[Phil Smith]

If I have understood correctly, it won't therefore work when Christmas Day and Boxing Day fall on a Thursday and Friday?

 

[CA_Punit]

=SMALL(IF(K4-1=$N$3:$N$7,K4-2,K4-1),1)-LARGE(IF(K4-2=$N$3:$N$7,1,),1)

Hope i have correctly computed the data...
If there are two holidays, i.e. wednessday and thursday at the same time this will not compute correctly.

If there are two holidays back to back