Simplifying the calculation in a complex formula to only sum hours where day is Saturday

=SUMIFS(MG3:MT3,MG$2:MT$2,">1",MG$2:MT$2,"<7",MG3:MT3,">8",MG3:MT3,"<=10")-(COUNTIFS(MG$2:MT$2,">1",MG$2:MT$2,"<7",MG3:MT3,">8",MG3:MT3,"<=10")*8)+COUNTIFS(MG$2:MT$2,">1",MG$2:MT$2,"<7",MG3:MT3,">10")*2+SUMIFS(MG3:MT3,MG$2:MT$2,7,MG3:MT3,"<=8")+(COUNTIFS(MG$2:MT$2,7,MG3:MT3,">8")*8)

Where row 1 is the date and row 2 is the day I am looking for MV3 (time and half column) to only look where the day is Saturday and sum what are the hours entered for those days.

The formula above summed the first 8 hours because >8 goes into double time. Now the business rules have changed (meh) and all hours regardless of amount and where the day worked is Saturday = time and a half.

So if there was only two saturdays on a paycycle and the operator worked 8 and 12 hours MV would show 20

Can you help?

Bingo...except some backtracking by me as I forgot about a small but vital element..

Looking at range where I enter hours of DM3:DZ3 and where a shift is a Monday to Friday and >8 =<10 hours then first 8 hours is basic then those two hours are calculated as Time and Half. Anything over 10 hours is then double

Is there a way to incorporate your formula in so if I work on a monday for 9 hours and say 10 on a Saturday that the Time and Half column (EB3) will show 19


=SUMPRODUCT(--(WEEKDAY(B1:J1)=7),B2:J2) +

Existing Formula

=SUMIFS(DM3:DZ3,DM$2:DZ$2,">1",DM$2:DZ$2,"<7",DM3:DZ3,">8",DM3:DZ3,"<=10")-(COUNTIFS(DM$2:DZ$2,">1",DM$2:DZ$2,"<7",DM3:DZ3,">8",DM3:DZ3,"<=10")*8)+COUNTIFS(DM$2:DZ$2,">1",DM$2:DZ$2,"<7",DM3:DZ3,">10")*2+SUMIFS(DM3:DZ3,DM$2:DZ$2,7,DM3:DZ3,"<=8")+(COUNTIFS(DM$2:DZ$2,7,DM3:DZ3,">8")*8)

Double Time formula (I will replace 7 with 6 so that it does not pick up Saturday hours)

=SUMIFS(DM3:DZ3,DM$2:DZ$2,">1",DM$2:DZ$2,"<7",DM3:DZ3,">10")-(COUNTIFS(DM$2:DZ$2,">1",DM$2:DZ$2,"<7",DM3:DZ3,">10")*10) +SUMIFS(DM3:DZ3,DM$2:DZ$2,7,DM3:DZ3,">8")-(COUNTIFS(DM$2:DZ$2,7,DM3:DZ3,">8")*8)+SUMIFS(DM3:DZ3,DM$2:DZ$2,1)

Of course, should have included before

Attached is a sample of the whole file

I've added dummy hours in for the Saturday 16th March to Monday 18th March (12 hours in each)

What I hope to have is

Basic = 8 (this behaviour is correct)
Time and Half O/T = 14 (12 for Saturday and 2 for Monday)
Double = 14 (12 for Sunday and 2 for Monday)

Hope that makes sense

No problem

Using MZ3:NM3 as an example where calcs are then displayed in NN3:NP3

See my previous post of "Calculating Overtime in one cell - is it possible"

Rules are for ->
Monday to Friday:
- first 8 are 'Basic' (NN3)
- 9 and 10 are time and half (NO3)
- >10 is double time (NP3)

Saturday - all hours are (this is where the business rule has changed) in time and half (NO4)

Sunday - all hours are in double time

The cells where the calcs are

NN3 - takes all the weekdays and sums all hours that are =<8
NO3 - takes all Saturday hours and all weekday hours >8 but <=10
NP3 - takes all Sunday hours and all weekday hours >10

Note that the code also needs to cater for text being entered in the cells (such as RDO, Hols, Sick etc)

NN3 - works fine
NO3 - needs to be fixed to sum all Saturday hours as at moment only takes first 8 (balance then goes to double time)
NP3 - I can fix this to now ignore Saturday by simply updating the day range it looks at, I believe

Hope it makes sense and can help

B

still unresolved...No one loves me!

Hi there

Can anyone help me, please. Would love to fix this so that Saturday sums all hours and not just upto 8.

in summary:

I have attached a sample spreadsheet and using MZ3:NM3 as an example this is the existing behaviour of the formulas in NN3:NP3

NN3 - Basic Hours: Sums all hours from Monday to Friday that are upto 8hrs daily. Eg: Monday to Friday 8 * 5 = 40. This cell ignores any hours that go over the maximum 8 as the balance is summed in NO3 or NP3 depending on total
NO3 - Time and Half: All Monday to Friday hours that are >8 but <10 and all Saturday hours that are <=8. Eg: Monday I work 9 hours and 6 hours on Saturday then NO3 = 7
NP3 - Double Time: All Sunday hours and any hours Monday to Friday >10 and Any Saturday >8

I would like to simplify this so that cell NO3 sums all hours that are logged on saturday and includes Monday to Friday hours where a shift has 2 time and haf hours

Otherwise all good!
Basic hours - this is fine
Double Time - I can change the formula to ignore Saturday (day 7)

Sorry for the late reply

But basically super - works great thanks! And thank you for the explanation as it helps to see how and where it all works

Otherwise for the double time I've removed this statement from the double time column as this ignores Saturday, right

+SUMIFS(NS3:OF3,NS$2:OF$2,7,NS3:OF3,">8")-(COUNTIFS(NS$2:OF$2,7,NS3:OF3,">8")*8)


Old Double time

=SUMIFS(NS3:OF3,NS$2:OF$2,">1",NS$2:OF$2,"<7",NS3:OF3,">10")-(COUNTIFS(NS$2:OF$2,">1",NS$2:OF$2,"<7",NS3:OF3,">10")*10) +SUMIFS(NS3:OF3,NS$2:OF$2,7,NS3:OF3,">8")-(COUNTIFS(NS$2:OF$2,7,NS3:OF3,">8")*8)+SUMIFS(NS3:OF3,NS$2:OF$2,1)

New Sunday/Double time

=SUMIFS(NS3:OF3,NS$2:OF$2,">1",NS$2:OF$2,"<7",NS3:OF3,">10")-(COUNTIFS(NS$2:OF$2,">1",NS$2:OF$2,"<7",NS3:OF3,">10")*10) +SUMIFS(NS3:OF3,NS$2:OF$2,1)

thanks again