Weekly reg/ot formula help please!

[Tiesthatbind]

Hello, if anyone can tell me where I have one wrong with this I woul
greatly appreciate the help! I need to sum total regular hrs for S>
then calculate where Saturdays hrs fall. Greater than 40 into weekl
ot, less than 40 into regular, and split Saturday's hrs into reg
(based on sum of S>F) and weekly OT. Here is my formula the proble
occurs in the 2nd IF, true part. $G$3 ( 40 hrs) - sum of L5:L10
regular hrs worked S>F ). problem when weekly reg hrs are under 32.
IF(SUM(L5:L10)>40,0,IF(SUM(L5:L10)<40,(40)-SUM(L5:L10),MIN(8,K11)))
Thank you in advance! :confused

 

[daddylonglegs]

Not sure I completely understand. Is the formula

=IF(SUM(L5:L10)>40,0,IF(SUM(L5:L10)<40,(40)-SUM(L5:L10),MIN(8,K11)))

designed to give Saturday hours which should be assigned to regular? Is
K11 Saturday hours, if so try

=IF(SUM(L5:L10)>40,0,40-MAX(0,SUM(L5:L10,K11)-40))

 

[Tiesthatbind]

Thank you for your reply Daddy Long legs, Yes, cell K11 is total
Saturday hrs. I would like cell L11 ( formula cell) to return only the
regular hrs worked on Saturday, based on the condition of whether or
not 40 reg hrs were worked in the S>Sat week. the total in Sat hrs may
have to be split depending on how many hrs were worked in the reg week.
I would like cell L11 to reflect only reg hrs worked on Sat. as the next
part of my sheet sums a $ amount for reg hrs per day. I appreciate your
example of a use of MAX as I'm not very famillar with it. Thank you
again, J

 

[daddylonglegs]

Hello again,

Can there only be 8 hours max assigned to regular hours from the
Saturday?

If so

=MIN(8,MEDIAN(K11,40-SUM(L5:L10),0))

 

[Tiesthatbind]

Daddy Long Legs, I take my hat off to you, the MIN/MEDIAN formula works
perfectly and I have also used it on Fridays hrs in case Sunday is
worked. Thank you very much!!!