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

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" wrote in
message ...

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

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 :)


--
kalika
------------------------------------------------------------------------
kalika's Profile: http://www.excelforum.com/member.php...o&userid=26651
View this thread: http://www.excelforum.com/showthread...hreadid=399380