Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
I need to keep my columns to a minimum.
There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
Hi,
For basic hours =MIN(SUM(A2:E2),38) For ot hours =MAX(SUM(A2:E2)-38,0) Mike "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
Assuming your headers (Mon, Tue, etc) were in row 1, and the number of
hours in row 2, with everything starting in A1, this formula would do what you want. =IF(SUM(A2:E2)<=40,SUM(A2:E2)&" Hours","40 regular hrs, "&(SUM(A2:E2)-40)& " Overtime Hrs") --JP On Oct 20, 10:49*am, Keep It Simple Stupid wrote: I need to keep my columns to a minimum. * There are 5 columns for each employee (Monday-Friday). *Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon * * Tue * *Wed * *Thu * *Fri 8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. *Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
Assuming data is in A2:E2, use these array* formulae:
F2: =SUM(IF(A2:E2<=8,A2:E2,8)) for sum of regular hours, and: G2: =SUM(IF(A2:E28,A2:E2-8)) for sum of overtime hours. * Array formula need to be committed using the key combination of Ctrl- Shift-Enter (CSE) instead of the usual Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula you will need to use CSE again. Hope this helps. Pete On Oct 20, 3:55*pm, Mike H wrote: Hi, For basic hours =MIN(SUM(A2:E2),38) For ot hours =MAX(SUM(A2:E2)-38,0) Mike "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. * There are 5 columns for each employee (Monday-Friday). *Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon * * Tue * *Wed * *Thu * *Fri 8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. *Any ideas?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
I'm not sure this is what I am looking for. I need the OT to be calculated
daily instead of weekly. The OT is based on 8 hour days. In my example, the total should be 38 regular hours and 8 OT hours - whereas if it was based on a weekly 40 hours it would have been 40 regular hours, 6 OT hours. Basically, I need a way to sum everything over 8 hours, subtracting 8 hours from each instance. "Mike H" wrote: Hi, For basic hours =MIN(SUM(A2:E2),38) For ot hours =MAX(SUM(A2:E2)-38,0) Mike "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
To get the total OT
=SUM(IF(A1:A58,A1:A5-8)) To get the total regular time =SUM(A1:A5)-SUM(IF(A1:A58,A1:A5-8)) both entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Keep It Simple Stupid" wrote in message ... I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
Forgot about the CSE. Darn it! This is an incredible help! Thanks a bunch
everyone! "Pete_UK" wrote: Assuming data is in A2:E2, use these array* formulae: F2: =SUM(IF(A2:E2<=8,A2:E2,8)) for sum of regular hours, and: G2: =SUM(IF(A2:E28,A2:E2-8)) for sum of overtime hours. * Array formula need to be committed using the key combination of Ctrl- Shift-Enter (CSE) instead of the usual Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula you will need to use CSE again. Hope this helps. Pete On Oct 20, 3:55 pm, Mike H wrote: Hi, For basic hours =MIN(SUM(A2:E2),38) For ot hours =MAX(SUM(A2:E2)-38,0) Mike "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas?- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
For regular hours:
=SUM(A1:E1)-(SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8)) For overtime hours: =SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8) Just press ENTER "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
Hi,
As said for the total overtime in F1 =SUM(IF(A1:E18,A1:E1-8,0)) but for the total regular time in G1 =SUM(A1:E1)-F1 Adjust your ranges as necessary. -- Thanks, Shane Devenshire "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
You're welcome - glad to help.
Pete On Oct 20, 4:19*pm, Keep It Simple Stupid wrote: Forgot about the CSE. *Darn it! *This is an incredible help! *Thanks a bunch everyone! "Pete_UK" wrote: Assuming data is in A2:E2, use these array* formulae: F2: * *=SUM(IF(A2:E2<=8,A2:E2,8)) for sum of regular hours, and: G2: * *=SUM(IF(A2:E28,A2:E2-8)) for sum of overtime hours. * Array formula need to be committed using the key combination of Ctrl- Shift-Enter (CSE) instead of the usual Enter. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you edit/amend the formula you will need to use CSE again. Hope this helps. Pete On Oct 20, 3:55 pm, Mike H wrote: Hi, For basic hours =MIN(SUM(A2:E2),38) For ot hours =MAX(SUM(A2:E2)-38,0) Mike "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. * There are 5 columns for each employee (Monday-Friday). *Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon * * Tue * *Wed * *Thu * *Fri 8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. *Any ideas?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of Daily Overtime
This one worked the best because the other ones were counting my 0's as
negative numbers, etc. (For overtime hours: =SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8) Thanks so much! "Teethless mama" wrote: For regular hours: =SUM(A1:E1)-(SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8)) For overtime hours: =SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8) Just press ENTER "Keep It Simple Stupid" wrote: I need to keep my columns to a minimum. There are 5 columns for each employee (Monday-Friday). Overtime is calculated over 8 hours and I need to track how much total regular time & over time for each employee for each month. I want some kind of sum if forumula that will calculate everything over 8 and everything under 8. Mon Tue Wed Thu Fri 8 10 12 6 10 Should be 38 regular hrs, 8 Overtime Hrs (employees will not always have at least 8 hours) I've tried max/min formulas but not sure how I can do it with keeping my columns/rows to a minmum. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I FREEZE DATA THAT CHANGES DAILY. dAILY/MONTHLY BUDGET | Excel Discussion (Misc queries) | |||
Overtime Calculation for Overtime | Excel Worksheet Functions | |||
Linking Daily Worksheet To Daily Invoice Total | Excel Worksheet Functions | |||
create a timesheet to add daily and weekly hours and overtime | Excel Worksheet Functions | |||
=SUMPRODUCT((Overtime!$J$6:$GY$6-DAY(Overtime!$J$6:$GY$6)+1=A25)*(Overtime!$J$7:$GY $2 | Excel Worksheet Functions |