# 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
###### Who is Participating?

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

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

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

Author 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.