Does my payday fall on a Bank Holiday?

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
Try this:

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

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
It supposed to.. unless its a bank holiday
 

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281

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
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
=SMALL(IF(K4-1=$N$3:$N$7,K4-2,K4-1),1)
 

Attachments

  • 1.png
    1.png
    7.6 KB · Views: 7

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows

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
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 13, 2004
Messages
281
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
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
=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
 

Watch MrExcel Video

Forum statistics

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

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
Top