Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
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(A5=40,SUM(A5-40),"0") Thanks in advance! Matt W -- BVHis ------------------------------------------------------------------------ BVHis's Profile: http://www.excelforum.com/member.php...fo&userid=8593 View this thread: http://www.excelforum.com/showthread...hreadid=499411 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
Try to use the following array function in cell A5
(assume that the standard full time is 40 hours) =SUM(IF((($B$5:$BA$5)40),$B$5:$BA$5-40)) Best regards, --- Yongjun CHEN Project Oriented Spreadsheet Development and Consulting - http://www.geocities.com/udqservices/UDQConsulting.htm ================================= "BVHis" wrote in message ... 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(A5=40,SUM(A5-40),"0") Thanks in advance! Matt W -- BVHis ------------------------------------------------------------------------ BVHis's Profile: http://www.excelforum.com/member.php...fo&userid=8593 View this thread: http://www.excelforum.com/showthread...hreadid=499411 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
=SUMPRODUCT(--(B5:BA540),--(B5:BA5))-SUMPRODUCT(--(B5:BA540))*40 or =SUMPRODUCT(--(B5:BA540),--(B5:BA5))-COUNTIF(B5:BA5,"40")*40 HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=499411 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
Hi
One way =SUM(B5:BA5)-COUNTIF(B5:BA5,"0")*40 Basically, add all the hours in the range then deduct 40 times the number of weeks that have data within them (0). -- Regards Roger Govier "BVHis" wrote in message ... 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(A5=40,SUM(A5-40),"0") Thanks in advance! Matt W -- BVHis ------------------------------------------------------------------------ BVHis's Profile: http://www.excelforum.com/member.php...fo&userid=8593 View this thread: http://www.excelforum.com/showthread...hreadid=499411 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
One way:
=SUMPRODUCT(--(B5:BA540),(B5:BA5-40)) In article , BVHis wrote: 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(A5=40,SUM(A5-40),"0") Thanks in advance! Matt W |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
This seems to work. It is an array formula so you must use
Ctrl-Shift-Enter to commit it. {=SUM(IF(B5:BA540,B5:BA5-40),0)} (Note the { } symbols are not entered but are automatically inserted when you commit with Ctrl-Shift-Enter.) - John www.JohnMichl.com |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
This assumes that the minimum number of hours worked is 40. If someone
worked 39 hours in a week, the total would not be correct. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
Thank you ALL for your replies! Your input was extremely helpful! Matt W -- BVHis ------------------------------------------------------------------------ BVHis's Profile: http://www.excelforum.com/member.php...fo&userid=8593 View this thread: http://www.excelforum.com/showthread...hreadid=499411 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help calculating overtime to date
You'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 Govier "John Michl" wrote in message oups.com... This assumes that the minimum number of hours worked is 40. If someone worked 39 hours in a week, the total would not be correct. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
overtime formula based on dynamic date range? | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Formula for Calculating a Floating Date | Excel Worksheet Functions | |||
Recurring annual events using a specific date as a trigger date | Excel Worksheet Functions | |||
Calculating Overtime from Hours total | Excel Worksheet Functions |