A few date queries for a payroll calendar

[mcinnes01]

Hi,

I am getting on with my payroll calendar but have a few date calculation questions.

1, My last working day in the month calculation works fine, except for july which brings a #Value! error.

my formula works for all the other months:

=WORKDAY('HOLIDAY FORMULA'!J10,CHOOSE(WEEKDAY('HOLIDAY FORMULA'!J10),-1,0,0,0,0,0-1),'HOLIDAY FORMULA'!$B$3:$B$11)

j10 = eomonth calc

2. How do I find bank holiday dates for christmas where christmas day is say saturday and boxing day is sunday, therefore i would need the bank holidays as monday and tuesday.

3. Finally how do I calculate 2 working days before a date, excluding bank holidays. My bank holiday calculations will be on a seperate sheet 'HOLIDAY FORMULA'!$B$3:$B$11


Thanks,


Andy

 

[mcinnes01]

During testing I found an error in my previous formula for boxing day, and neglected to include what to do if boxing day is a monday. So essentially if boxing day is ever a monday the boxing day bank holiday will be tuesday.

=IF(WEEKDAY(DATE($I$4,12,26))=7,DATE($I$4,12,26+2),IF(WEEKDAY(DATE($I$4,12,26))=1,DATE($I$4,12,26+2),IF(WEEKDAY(DATE($I$4,12,26))=2,DATE($I$4,12,26+1),DATE($I$4,12,26))))

 

[DonkeyOte]

I believe this has already been covered in the other thread, ie for Boxing Day:

=DATE($I$4,12,26)+LOOKUP(WEEKDAY(DATE($I$4,12,26),2),{1,2,6},{1,0,2})

so to reiterate - if Boxing Day falls on Sat/Sun then associated holiday must be 28th, if it falls on Monday it must be 27th else it is the 26th.