Calculating labour costs

Hi all

I'm working on a spreadsheet that will calculate my labour cost.
Everything worked fine until I have tried to apply Bank holidays pay.
The formula that calculates Basic Hours for person is:

Please Login or Register  to view this content.

where first "if" is checking if H2 (Monday) is ticked as a BH

Please Login or Register  to view this content.

if is then check what shift pattern apply and if total hours (T4) minus BH hours (H4) are less than contracted hours.

Please Login or Register  to view this content.

if that is the case then total hours (T4) minus BH hours(H4) are you result
if that is not the case then overtime apply and you basic hours are your contracted hours and overtimes are calculated in separate cell.

And that formula works perfectly if there is one BH per week and it is on Monday.
I have no idea how perform those checks for BH to be a random day of the week or there are two in one the week.

Probably there is a easier way to do it that slip my perception, any thoughts?

Looking at your example you could use similar formula to these:

Total hours

=SUMPRODUCT(($F4:$S4)*($F3:$S3<>"RTL"))

(not sure what RTL is so I ignored it in the formula)

Sunday/BH

=SUMPRODUCT(($F4:$S4),(($F3:$S3="Sunday")+($F2:$S2="y")))

I changed the "wingdings" to a simply "y2 (Calibri)

This calculates hours for SUNDAY + any day designated as BH (with the "y")

I would also advise removing merged cells as they can cause problems when using formulae.