Home |
Search |
Today's Posts |
#1
|
|||
|
|||
overtime formula based on dynamic date range?
Is there a way to formulate which portion of a day's hours worked are overtime hours? Here is an example. I'm looking for a formula for the overtime (col.F) that will sum any hourly values (col.E) over 40 hrs for the same week, with Monday being the first day of the week. I've tried using SUMIF, SUMPRODUCT, LOOKUP, WEEKNUM, etc., but I'm a newbie so it never works ;). Also the workweek is not always the same so I can't just sum a set 5-cell range. Date Day TimeIn Time Out Total Hrs. Overtime 8/01 Mon 2:56p 1:53a 10.95 8/02 Tue 2:58p 1:25a 10.45 8/03 Wed 5:00p 10:15p 5.25 8/04 Thu 4:45p 11:15p 6.50 8/05 Fri 3:08p 1:04a 9.93 3.08 8/06 Sat 4:07p 11:04p 6.95 6.95 8/07 Sun 2:59p 1:31a 10.53 10.53 8/09 Tue 4:50p 9:48p 4.96 8/10 Wed 3:10p 12:55a 9.75 8/11 Thu 4:57p 10:30p 5.55 8/12 Fri 3:09p 3:32a 12.38 8/14 Sun 3:04p 1:14a 10.16 2.81 TIA-- kalika +-------------------------------------------------------------------+ |Filename: Untitled.jpg | |Download: http://www.excelforum.com/attachment.php?postid=3747 | +-------------------------------------------------------------------+ -- kalika ------------------------------------------------------------------------ kalika's Profile: http://www.excelforum.com/member.php...o&userid=26651 View this thread: http://www.excelforum.com/showthread...hreadid=399380 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for filtering and a defaulting date | Excel Worksheet Functions | |||
MIN within range based on criteria | Excel Discussion (Misc queries) | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
Need to find Min value based on date range entered | Excel Worksheet Functions |