overtime formula based on dynamic date range?

[kalika]

Is there a way to formulate which portion of a day's hours worked ar
overtime hours? Here is an example. I'm looking for a formula for th
overtime (col.F) that will sum any hourly values (col.E) over 40 hrs fo
the same week, with Monday being the first day of the week. I've trie
using SUMIF, SUMPRODUCT, LOOKUP, WEEKNUM, etc., but I'm a newbie so i
never works . Also the workweek is not always the same so I can'
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--
kalik

+-------------------------------------------------------------------
|Filename: Untitled.jpg
|Download: http://www.excelforum.com/attachment.php?postid=3747
+-------------------------------------------------------------------

 

[Bob Phillips]

Kalika,

If you can add another helper column, this should do it.

In F2, add =E2
In F3, add
=IF((IF(WEEKDAY(A3)=2,A3,A3-WEEKDAY(A3-2)))=(IF(WEEKDAY(A2)=2,A2,A2-WEEKDAY(
A2-2))),E3+F2,E3)
and copy that down

Then in G2, add =MAX(0,F2-40)
and copy that down

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[kalika]

Thank you Bob!!
That worked beautifully, and i don't mind the extra column.
I changed the formula in G2 to:
=IF((MAX(0,F2-40))>E2,E2,(MAX(0,F2-40)))
to get the portion of the day spent in overtime.
But that weekly tally of hours had me really confused.
I'm gonna have to study that formula and figure it out.

Thanks again--
kalika