# Does my payday fall on a Bank Holiday?

#### Phil Smith

##### Active Member
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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### steve the fish

##### Well-known Member
Try this:

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

#### Phil Smith

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

Last edited:

#### steve the fish

##### Well-known Member
It supposed to.. unless its a bank holiday

#### Phil Smith

##### Active Member

ADVERTISEMENT

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

##### Well-known Member
=SMALL(IF(K4-1=\$N\$3:\$N\$7,K4-2,K4-1),1)

#### Attachments

• 1.png
7.6 KB · Views: 7

#### CA_Punit

##### Well-known Member

ADVERTISEMENT

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

##### MrExcel MVP, Moderator
Maybe
+Fluff.xlsm
ABCDEF
227/11/2015
324/12/201525/12/2015
422/01/201626/12/2015
519/02/201601/01/2016
617/03/201618/03/2016
715/04/2016
813/05/2016
910/06/2016
1008/07/2016
1105/08/2016
1202/09/2016
1330/09/2016
1428/10/2016
1525/11/2016
16
List
Cell Formulas
RangeFormula
A3:A15A3=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,F3:F6)
Dynamic array formulas.

#### Phil Smith

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

#### CA_Punit

##### Well-known Member
=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

Replies
1
Views
433
Replies
5
Views
481
Replies
1
Views
2K
Replies
6
Views
23K
Replies
2
Views
198

Threads
1,129,936
Messages
5,639,084
Members
417,072
Latest member
JaimeDee

### 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

### 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