Calculating Saturday hours to correct overtime - bigger brains than mine required!

Hi there

Any help would be very much appreciated! I have a spreadsheet (attached) I have used for many years (with the help of you guys at the time) but now there has been a change in the way my guys have to be paid on Saturdays.

Existing conditions in the attached were:

Monday to Friday the first 8 hours = Basic Hours
Monday to Friday >8, <=10 = Time & Half
Monday to Friday >12 = Double
Saturday = All Time & Half
Sunday = All Double

HOWEVER... now every Saturday the first two hours are paid Time and Half and thereafter they are on Double Time.

ie: New Condition is Saturday <=2 = Time & Half thereafter >2 = Double

What changes do I need to make to the Time & Half formula, please!

Basic Hours =SUMIFS(J3:W3,J$2:W$2,">1",J$2:W$2,"<7",J3:W3,"<=8")+COUNTIFS(J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">8")*8

Time & Half =SUMIFS(J3:W3,J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">8",J3:W3,"<=10")-(COUNTIFS(J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">8",J3:W3,"<=10")*8)+COUNTIFS(J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">10")*2+SUMIF(J$2:W$2,7,J3:W3)

Double =SUMIFS(J3:W3,J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">10")-(COUNTIFS(J$2:W$2,">1",J$2:W$2,"<7",J3:W3,">10")*10) +SUMIFS(J3:W3,J$2:W$2,1)


Pierre

Hey

One flaw which I didn't not flag earlier, sorry. Sometimes there will be text in the cells. Ie: someone is "sick" (AA) there is conditional formatting to count that or "hols" (AB). Thereafter it being a work spreadsheet there can be general text written in as reminders etc.

So though brilliant, text entered into any cells means it returns an error. Damn! Help!

Pierre

Hi JeteMc

The Array doesn't do much my end - I am working off a mac so imagine that makes a difference, however the formulae work upto a point...

I have attached the xls and you 'll se everything works great, unless I put hours in which are <8 in the weekdays - the hours go into the Double time column(Z)! Go figure...any ideas?

B

Ah well yes, I have absolutely no idea what you've just told me there. Totally gone over my head, I'm afraid....

Originally Posted by JeteMc

Sorry about that. I assume that you highlighted cell Z9 to illustrate the issue with the double overtime. My comment was that if you array enter cell X9 the value displayed there will change from 40 to 62 and that the value in cell Z9 will change from 50 to 28. I have array entered both of the formulas in cells X9 and Y9 so that you can see what I mean.
Let us know if you have any questions.

Hey

Just figured out how to actually do the CSE, or more importantly where the cursor needs to be...before the "="!

All good, can't fault it all - can't thank you enough

Have a good week

Baiona