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.
 

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
What dates would you expect?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
Will the first date populated always be a friday?
 

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
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

Active Member
Joined
Aug 13, 2004
Messages
281

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

+Fluff.xlsm
ABCDEF
227/11/201527/11/201524/12/2015
323/12/201523/12/201525/12/2015
422/01/201622/01/201626/12/2015
519/02/201619/02/201601/01/2016
617/03/201617/03/201618/03/2016
715/04/201615/04/2016
813/05/201613/05/2016
910/06/201610/06/2016
1008/07/201608/07/2016
1105/08/201605/08/2016
1202/09/201602/09/2016
1330/09/201630/09/2016
1428/10/201628/10/2016
1525/11/201625/11/2016
List
Cell Formulas
RangeFormula
A3:A15A3=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,F2:F6)
B3:B15B3=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

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
I agree, just waiting on the OP.
 

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,288
Members
417,135
Latest member
zeusmining

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