Need overtime formula help

[TSuraco]

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.

 

[RagDyeR]

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 !
-----------------------------------------------------------------------------------------------

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.

 

[Ron Coderre]

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]

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

 

[Ron Coderre]

Please ignore the MROUND comment....I decided against using that function.

Regards,

Ron



(clipped)

 

[Ron Coderre]

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