Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly reg/ot formula help please!
Hello, if anyone can tell me where I have one wrong with this I would greatly appreciate the help! I need to sum total regular hrs for SF then calculate where Saturdays hrs fall. Greater than 40 into weekly ot, less than 40 into regular, and split Saturday's hrs into reg. (based on sum of SF) and weekly OT. Here is my formula the problem occurs in the 2nd IF, true part. $G$3 ( 40 hrs) - sum of L5:L10 ( regular hrs worked SF ). problem when weekly reg hrs are under 32. IF(SUM(L5:L10)40,0,IF(SUM(L5:L10)<40,([b]40)-SUM(L5:L10),MIN(8,K11))) Thank you in advance! -- Tiesthatbind ------------------------------------------------------------------------ Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360 View this thread: http://www.excelforum.com/showthread...hreadid=521215 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly reg/ot formula help please!
Not sure I completely understand. Is the formula =IF(SUM(L5:L10)40,0,IF(SUM(L5:L10)<40,([b]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)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521215 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly reg/ot formula help please!
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 SSat 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 -- Tiesthatbind ------------------------------------------------------------------------ Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360 View this thread: http://www.excelforum.com/showthread...hreadid=521215 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly reg/ot formula help please!
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)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521215 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly reg/ot formula help please!
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!!! :) :) :) -- Tiesthatbind ------------------------------------------------------------------------ Tiesthatbind's Profile: http://www.excelforum.com/member.php...o&userid=32360 View this thread: http://www.excelforum.com/showthread...hreadid=521215 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |