Basis Of Estimate with hours in Excel

So I have this spreadsheet that I need hours calculated based on a hourly rate.

On row 11 I want to be able to change the hours accordingly with column D having the hourly rate and update the cells with the new cost.  The calculation should include 1.5 times anything over 40 hours.

For example:  45 in cell K11on row 11 = 40 X $125 and 5 x 187.5 = $5,937.50
C--Users-lfreund-Desktop-BOE.xlsx
LUIS FREUNDAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Simpler formula:

=MIN(F$11,40)*$D12+MAX(0,F$11-40)*$D12*1.5

Even simpler:

=(F$11*$D12)+MAX(0,F$11-40)*$D12/2


Thanks
Rob
0
 
abbas abdullaConnect With a Mentor Commented:
Hi Luis,
in cell F12 paste this formula =IF(F$11<=40,$D12*F$11,SUM((40*$D12),MOD(F11,40)*$D12*1.5))
then drag it down and right.

Br,
Abbas
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Simplified some of your other formulas as well.

Row 7 Dates, converted to using CHOOSE function rather than multiple IF statements:
F7 =CHOOSE(MATCH($D5,{"Daily","Weekly","Monthly","Quarterly"},0),D4+(D7-1),D4+7*(D7-1),EDATE($D$4,($D$7-1)),EDATE($D$4,3*($D$7-1)))
G7:X7 =CHOOSE(MATCH($D5,{"Daily","Weekly","Monthly","Quarterly"},0),F7+1,F7+7,EDATE($D$4,G9-1),EDATE($D$4,3*(G9-1)))

Row 8 dates, don't need the LEFT and RIGHT functions to convert the date into a string:
=DAY(F7)&CHAR(10)&TEXT(F7,"mmm")&CHAR(10)&TEXT(F7,"'yy")

TEXT(F7,"mmm")  will only be 3 characters so don't need to use LEFT(#,3) to get 3 characters
Note in the TEXT(F7,"'yy")  I have included the ' in the text format parameter.

Conditional formatting to get red lines:
=FLOOR(TODAY(),7)+2=F$7

FLOOR function rounds a number down by the specified factor, in this case 7, which rounds down to the previous Saturday; by adding two you then get the Monday date.
0
 
LUIS FREUNDAuthor Commented:
Thanks Gents....extremely appreciative!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.