| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need overtime formula help
I keep time on a project that has more than one job. Example:
Mike Mon Tue Wed Thur Fri Alky 6 6 6 6 6 = 30 Shu 2 2 2 2 2 = 10 Chfu 2 2 2 2 2 = 10 -------------------------------------------------- 10 10 10 10 10 50 On my summary sheet, I already have a formula to break out the 10 OT hours. Mike ST OT 40 10 What I need is a formula to calculate OT on a daily basis for each project and put it into a seperate OT column. Mike Mon Tue Wed Thur Fri ST OT ST OT ST OT ST OT ST OT ST OT Alky 5 1 5 1 5 1 5 1 5 1 = 25 5 Shu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 Chfu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 ------------------------------------------------------------------- 10 10 10 10 10 50=40 10 Something like that. we have to break out & charge overtime to individual jobs, instead of all to one. I'm so close, but I am just missing the mark. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need overtime formula help
What criteria did you use to determine that 1 hour of the 6 was OT?
Why not 2 or 0.75? Same question for the other 2 OT daily values. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "TSuraco" wrote in message ... I keep time on a project that has more than one job. Example: Mike Mon Tue Wed Thur Fri Alky 6 6 6 6 6 = 30 Shu 2 2 2 2 2 = 10 Chfu 2 2 2 2 2 = 10 -------------------------------------------------- 10 10 10 10 10 50 On my summary sheet, I already have a formula to break out the 10 OT hours. Mike ST OT 40 10 What I need is a formula to calculate OT on a daily basis for each project and put it into a seperate OT column. Mike Mon Tue Wed Thur Fri ST OT ST OT ST OT ST OT ST OT ST OT Alky 5 1 5 1 5 1 5 1 5 1 = 25 5 Shu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 Chfu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 ------------------------------------------------------------------- 10 10 10 10 10 50=40 10 Something like that. we have to break out & charge overtime to individual jobs, instead of all to one. I'm so close, but I am just missing the mark. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need overtime formula help
well, i decided that 60% of the time was on 1 project, 20% was on the second
& 20% on the third. Then I rounded to the nearest tenth "RagDyeR" wrote: What criteria did you use to determine that 1 hour of the 6 was OT? Why not 2 or 0.75? Same question for the other 2 OT daily values. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "TSuraco" wrote in message ... I keep time on a project that has more than one job. Example: Mike Mon Tue Wed Thur Fri Alky 6 6 6 6 6 = 30 Shu 2 2 2 2 2 = 10 Chfu 2 2 2 2 2 = 10 -------------------------------------------------- 10 10 10 10 10 50 On my summary sheet, I already have a formula to break out the 10 OT hours. Mike ST OT 40 10 What I need is a formula to calculate OT on a daily basis for each project and put it into a seperate OT column. Mike Mon Tue Wed Thur Fri ST OT ST OT ST OT ST OT ST OT ST OT Alky 5 1 5 1 5 1 5 1 5 1 = 25 5 Shu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 Chfu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 ------------------------------------------------------------------- 10 10 10 10 10 50=40 10 Something like that. we have to break out & charge overtime to individual jobs, instead of all to one. I'm so close, but I am just missing the mark. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need overtime formula help
With
A1:F7 containing your posted Mon-Fri data Row_1 contains titles: Mike, Mon, Tue, etc A3:A5 contains project names: Alky, Shu, etc Row_7 contains totals by day Set up this structure in A9:C13 Mike Mon Mon Type ST OT Alky Shu Chfu It appears that you are rounding to the nearest half-hour, so you'd need to enter hours worked in half-hour increments or the rounding will create incorrect results. This formula returns the allocated (if necessary) standard hours: B11: =ROUND(IF(SUMIF($A$1:$F$1,B$9,$A$7:$F$7)8, VLOOKUP($A11,$A$1:$F$7,MATCH(B$9,$A$1:$F$1,0),0)/ SUMIF($A$1:$F$1,B$9,$A$7:$F$7)*8,VLOOKUP($A11,$A$1 :$F$7, MATCH(B$9,$A$1:$F$1,0),0))/0.5,0)*0.5 This formula returns the allocated (if necessary) OT hours: C11: =ROUND(IF(SUMIF($A$1:$F$1,B$9,$A$7:$F$7)8, VLOOKUP($A11,$A$1:$F$7,MATCH(C$9,$A$1:$F$1,0),0)/ SUMIF($A$1:$F$1,B$9,$A$7:$F$7)*(SUMIF($A$1:$F$1,B$ 9,$A$7:$F$7)-8), VLOOKUP($A11,$A$1:$F$7,MATCH(C$9,$A$1:$F$1,0),0))/0.5,0)*0.5 Copy those formulas down as far as needed and into the appropriate columns to the right. NOTE: The MROUND function is part of the Analysis ToolPak add-in. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "TSuraco" wrote in message ... I keep time on a project that has more than one job. Example: Mike Mon Tue Wed Thur Fri Alky 6 6 6 6 6 = 30 Shu 2 2 2 2 2 = 10 Chfu 2 2 2 2 2 = 10 -------------------------------------------------- 10 10 10 10 10 50 On my summary sheet, I already have a formula to break out the 10 OT hours. Mike ST OT 40 10 What I need is a formula to calculate OT on a daily basis for each project and put it into a seperate OT column. Mike Mon Tue Wed Thur Fri ST OT ST OT ST OT ST OT ST OT ST OT Alky 5 1 5 1 5 1 5 1 5 1 = 25 5 Shu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 Chfu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 ------------------------------------------------------------------- 10 10 10 10 10 50=40 10 Something like that. we have to break out & charge overtime to individual jobs, instead of all to one. I'm so close, but I am just missing the mark. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need overtime formula help
Please ignore the MROUND comment....I decided against using that function.
Regards, Ron "Ron Coderre" wrote in message ... (clipped) NOTE: The MROUND function is part of the Analysis ToolPak add-in. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need overtime formula help
Correction of the overtime allocation formula:
(It was calculating overtime when none was worked) It should be: C11: =ROUND(IF(SUMIF($A$1:$F$1,C$9,$A$7:$F$7)8, VLOOKUP($A11,$A$1:$F$7,MATCH(C$9,$A$1:$F$1,0),0)/ SUMIF($A$1:$F$1,C$9,$A$7:$F$7)*(SUMIF($A$1:$F$1,C$ 9,$A$7:$F$7)-8),)/0.5,0)*0.5 Regards, Ron Microsoft MVP - Excel "Ron Coderre" wrote in message ... With A1:F7 containing your posted Mon-Fri data Row_1 contains titles: Mike, Mon, Tue, etc A3:A5 contains project names: Alky, Shu, etc Row_7 contains totals by day Set up this structure in A9:C13 Mike Mon Mon Type ST OT Alky Shu Chfu It appears that you are rounding to the nearest half-hour, so you'd need to enter hours worked in half-hour increments or the rounding will create incorrect results. This formula returns the allocated (if necessary) standard hours: B11: =ROUND(IF(SUMIF($A$1:$F$1,B$9,$A$7:$F$7)8, VLOOKUP($A11,$A$1:$F$7,MATCH(B$9,$A$1:$F$1,0),0)/ SUMIF($A$1:$F$1,B$9,$A$7:$F$7)*8,VLOOKUP($A11,$A$1 :$F$7, MATCH(B$9,$A$1:$F$1,0),0))/0.5,0)*0.5 This formula returns the allocated (if necessary) OT hours: C11: =ROUND(IF(SUMIF($A$1:$F$1,B$9,$A$7:$F$7)8, VLOOKUP($A11,$A$1:$F$7,MATCH(C$9,$A$1:$F$1,0),0)/ SUMIF($A$1:$F$1,B$9,$A$7:$F$7)*(SUMIF($A$1:$F$1,B$ 9,$A$7:$F$7)-8), VLOOKUP($A11,$A$1:$F$7,MATCH(C$9,$A$1:$F$1,0),0))/0.5,0)*0.5 Copy those formulas down as far as needed and into the appropriate columns to the right. NOTE: The MROUND function is part of the Analysis ToolPak add-in. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "TSuraco" wrote in message ... I keep time on a project that has more than one job. Example: Mike Mon Tue Wed Thur Fri Alky 6 6 6 6 6 = 30 Shu 2 2 2 2 2 = 10 Chfu 2 2 2 2 2 = 10 -------------------------------------------------- 10 10 10 10 10 50 On my summary sheet, I already have a formula to break out the 10 OT hours. Mike ST OT 40 10 What I need is a formula to calculate OT on a daily basis for each project and put it into a seperate OT column. Mike Mon Tue Wed Thur Fri ST OT ST OT ST OT ST OT ST OT ST OT Alky 5 1 5 1 5 1 5 1 5 1 = 25 5 Shu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 Chfu 1.5 .5 1.5 .5 1.5 .5 1.5 .5 1.5 .5 = 7.5 2.5 ------------------------------------------------------------------- 10 10 10 10 10 50=40 10 Something like that. we have to break out & charge overtime to individual jobs, instead of all to one. I'm so close, but I am just missing the mark. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| overtime hours formula | Excel Worksheet Functions | |||
| Need help with Overtime formula. | 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 | |||
| HOW DO I CALULATE A OVERTIME FORMULA | Excel Worksheet Functions | |||
| IF or SUMIF help for Overtime Formula | Excel Worksheet Functions | |||
Hybrid Mode
