software

So here's the deal... I need to calculate the total number of overtime
hours to date. I've searched the threads but have come up short with
what I need to accomplish.

My spreadsheet has a TOTAL OVERTIME cell (A5). Cells B5 through BA5
represent the 52 weeks in a year. Each time a value is entered in any
of those cells, I would like the TOTAL OVERTIME value to update
(overtime is anything over 40 hours). So if 41 is entered in B5, the
total overtime to date would be 1. If 44 is entered in C5, the total
overtime to date would be 5 hours (1 hour from the previous week and 4
hours from this week).

This is the formula I found, but this only does one 40 hour period. I
need something a little more complex.

=IF(A5gt;=40,SUM(A5-40),quot;0quot;)

Thanks in advance!

Matt W--
BVHis
------------------------------------------------------------------------
BVHis's Profile: www.excelforum.com/member.php...foamp;userid=8593
View this thread: www.excelforum.com/showthread...hreadid=499411Try to use the following array function in cell A5
(assume that the standard full time is 40 hours)

=SUM(IF((($B$5:$BA$5)gt;40),$B$5:$BA$5-40))

Best regards,
---
Yongjun CHEN
Project Oriented Spreadsheet Development and Consulting- www.geocities.com/udqservices/UDQConsulting.htm
=================================
quot;BVHisquot; gt; wrote in
message ...
gt;
gt; So here's the deal... I need to calculate the total number of overtime
gt; hours to date. I've searched the threads but have come up short with
gt; what I need to accomplish.
gt;
gt; My spreadsheet has a TOTAL OVERTIME cell (A5). Cells B5 through BA5
gt; represent the 52 weeks in a year. Each time a value is entered in any
gt; of those cells, I would like the TOTAL OVERTIME value to update
gt; (overtime is anything over 40 hours). So if 41 is entered in B5, the
gt; total overtime to date would be 1. If 44 is entered in C5, the total
gt; overtime to date would be 5 hours (1 hour from the previous week and 4
gt; hours from this week).
gt;
gt; This is the formula I found, but this only does one 40 hour period. I
gt; need something a little more complex.
gt;
gt; =IF(A5gt;=40,SUM(A5-40),quot;0quot;)
gt;
gt; Thanks in advance!
gt;
gt; Matt W
gt;
gt;
gt; --
gt; BVHis
gt; ------------------------------------------------------------------------
gt; BVHis's Profile:
gt; www.excelforum.com/member.php...foamp;userid=8593
gt; View this thread: www.excelforum.com/showthread...hreadid=499411
gt;

=SUMPRODUCT(--(B5:BA5gt;40),--(B5:BA5))-SUMPRODUCT(--(B5:BA5gt;40))*40

or

=SUMPRODUCT(--(B5:BA5gt;40),--(B5:BA5))-COUNTIF(B5:BA5,quot;gt;40quot;)*40

HTH

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=499411Hi

One way
=SUM(B5:BA5)-COUNTIF(B5:BA5,quot;gt;0quot;)*40

Basically, add all the hours in the range then deduct 40 times the
number of weeks that have data within them (gt;0).

--
Regards

Roger Govierquot;BVHisquot; gt; wrote in
message ...
gt;
gt; So here's the deal... I need to calculate the total number of
gt; overtime
gt; hours to date. I've searched the threads but have come up short with
gt; what I need to accomplish.
gt;
gt; My spreadsheet has a TOTAL OVERTIME cell (A5). Cells B5 through BA5
gt; represent the 52 weeks in a year. Each time a value is entered in any
gt; of those cells, I would like the TOTAL OVERTIME value to update
gt; (overtime is anything over 40 hours). So if 41 is entered in B5, the
gt; total overtime to date would be 1. If 44 is entered in C5, the total
gt; overtime to date would be 5 hours (1 hour from the previous week and 4
gt; hours from this week).
gt;
gt; This is the formula I found, but this only does one 40 hour period. I
gt; need something a little more complex.
gt;
gt; =IF(A5gt;=40,SUM(A5-40),quot;0quot;)
gt;
gt; Thanks in advance!
gt;
gt; Matt W
gt;
gt;
gt; --
gt; BVHis
gt; ------------------------------------------------------------------------
gt; BVHis's Profile:
gt; www.excelforum.com/member.php...foamp;userid=8593
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=499411
gt;
One way:

=SUMPRODUCT(--(B5:BA5gt;40),(B5:BA5-40))

In article gt;,
BVHis gt; wrote:

gt; So here's the deal... I need to calculate the total number of overtime
gt; hours to date. I've searched the threads but have come up short with
gt; what I need to accomplish.
gt;
gt; My spreadsheet has a TOTAL OVERTIME cell (A5). Cells B5 through BA5
gt; represent the 52 weeks in a year. Each time a value is entered in any
gt; of those cells, I would like the TOTAL OVERTIME value to update
gt; (overtime is anything over 40 hours). So if 41 is entered in B5, the
gt; total overtime to date would be 1. If 44 is entered in C5, the total
gt; overtime to date would be 5 hours (1 hour from the previous week and 4
gt; hours from this week).
gt;
gt; This is the formula I found, but this only does one 40 hour period. I
gt; need something a little more complex.
gt;
gt; =IF(A5gt;=40,SUM(A5-40),quot;0quot;)
gt;
gt; Thanks in advance!
gt;
gt; Matt W

This seems to work. It is an array formula so you must use
Ctrl-Shift-Enter to commit it.

{=SUM(IF(B5:BA5gt;40,B5:BA5-40),0)}

(Note the { } symbols are not entered but are automatically inserted
when you commit with Ctrl-Shift-Enter.)

- John
www.JohnMichl.comThis assumes that the minimum number of hours worked is 40. If someone
worked 39 hours in a week, the total would not be correct.
Thank you ALL for your replies! Your input was extremely helpful!

Matt W--
BVHis
------------------------------------------------------------------------
BVHis's Profile: www.excelforum.com/member.php...foamp;userid=8593
View this thread: www.excelforum.com/showthread...hreadid=499411You're quite right, John, my argument is flawed.
Your's, and the other solutions posted will solve the OP's problem
without the error that mine would potentially have had.
--
Regards

Roger Govierquot;John Michlquot; gt; wrote in message oups.com...
gt; This assumes that the minimum number of hours worked is 40. If
gt; someone
gt; worked 39 hours in a week, the total would not be correct.
gt;