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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,728
Office Version
  1. 365
Platform
  1. Windows
All 3 formulae give the same result
+Fluff.xlsm
ABCDEF
1
227/11/201527/11/201527/11/201524/12/2015
323/12/201523/12/201523/12/201525/12/2015
422/01/201622/01/201622/01/201626/12/2015
519/02/201619/02/201619/02/201601/01/2016
617/03/201617/03/201617/03/201618/03/2016
715/04/201615/04/201615/04/2016
813/05/201613/05/201613/05/2016
910/06/201610/06/201610/06/2016
1008/07/201608/07/201608/07/2016
1105/08/201605/08/201605/08/2016
1202/09/201602/09/201602/09/2016
1330/09/201630/09/201630/09/2016
1428/10/201628/10/201628/10/2016
1525/11/201625/11/201625/11/2016
16
List
Cell Formulas
RangeFormula
A3:A15A3=WORKDAY.INTL(SEQUENCE(13,,A2+29,28),-1,1,Bank_Holidays)
B3:B15B3=WORKDAY($B$2+1+ROWS($B$1:B1)*28,-1,Bank_Holidays)
C3:C15C3=IF(WEEKDAY(C2,2)=3,C2+30,IF(WEEKDAY(C2,2)=4,C2+29,IF(AND(WEEKDAY(C2,2)=5,COUNTIF(Bank_Holidays,C2+28),COUNTIF(Bank_Holidays,C2+27)),C2+26,IF(AND(WEEKDAY(C2,2)=5,COUNTIF(Bank_Holidays,C2+28),NOT(COUNTIF(Bank_Holidays,C2+27))),C2+27,C2+28))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
Bank_Holidays=List!$F$2:$F$6A3, B3:C15
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Phil Smith

Active Member
Joined
Aug 13, 2004
Messages
281
Thank you! Both your solutions did work. Fluff, I must have done something wrong when I tried first of all. Apologies!
I dont have a clue as to how your formulas work, but they are both more concise than mine!
Thank you once again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,728
Office Version
  1. 365
Platform
  1. Windows
The formula in col B above is not mine but from @steve the fish ;)

Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,895
Messages
5,638,874
Members
417,057
Latest member
Jimbo Jones

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