Change date if it is a weekend or bank holiday

[Braddles1985]

Hi,
I have set up a pay date sheet which shows when monthly pay is due up to 2030.
I want to bring the date forward to the prior working day if the pay date falls on a weekend or bank holiday.
Bank holidays have been set in a range called "Hols"..

I can get it to move the pay date to the Friday if falling on a weekend, but cannot see how to add the bank holidays aswell... any help?

I had seen one which used something similar to below but cannot see where i put in the "hols" (my pay dates are in column S); but also if the pay date is the saturday of a friday bank holiday will it move the pay date to Thursday?

=S5+IF(MOD(S5,7)=0,-1,IF(MOD(S5,7)=1,-2))


Help?

Thanks

 

[steve the fish]

Try this

=WORKDAY(S5+1,-1,holidays)