General Help

[ehlfb]

Dear All,

I have in Cell A2 132:23 hours, I need to calculate in Cell B2 the following:

First 60 Hours at the rate of 3
Second 60 hours at the rate of 5
anything > 120 at the rate of 9

the total should be 591.45

Can anyone help me please?.

 

[jimrward]

can you show how you have derived your answer

eg (60*3) + (60*5) + (12.23 *9)

 

[Craigjre]

Try this, substitute B2 into the formula (instead of A11)

=IF(A11<60,A11*3,IF(AND(A11>60,A11<120),(60*3+(A11-60)*5),IF(A11>120,(60*3+60*5+(A11-120)*9))))

 

[Sharpefiction]

I split out the formulas into 3, then added up those values:
(on my workings I used cells A4 to A7 to host the formulas)

'=IF(A2>0,IF(A2<60,SUM(A2*3),IF(A2>60,SUM(60*3),""))) - for the 1st 60
'=IF(A2>60,IF(A2<120,SUM(A2-60)*5,IF(A2>120,SUM(60*5),""))) - for the 2nd 60
'=IF(A2>120,SUM(A2-120)*9,"") - for the 3rd 60
'=SUM(A4:A6) - to provide a total for the above calculations

I make the answer 590.07 though...

 

[Craigjre]

You're coming up with 590.07 because you have to consider the 23 minutes as (23/60) hours

 

[ehlfb]

Hi Jimrward,

Actually I calculated manually

60 hours * 3 = 180
60 hours * 5= 300
12 hours and 23/60 minutes becames 12 +38/100 * 9 = 111.45
This gives the total of 591.45

 

[Peter_SSs]

Try this. After entering the formula, you may need to format the cell as Number.

=A2*24*3+MAX(A2*24-60,0)*2+MAX(A2*24-120,0)*4

 

[Sharpefiction]

But are we not saying that 132.23 is:

One hundred and thirty-two hours and 23 minutes? Therefore, you wouldn't need to consider the 23 as a fraction of 60 for the calculation!?

Might be having a bald moment, but think I'm missing something otherwise..

 

[Peter_SSs]

But are we not saying that 132.23 is:

One hundred and thirty-two hours and 23 minutes? Therefore, you wouldn't need to consider the 23 as a fraction of 60 for the calculation!?

Might be having a bald moment, but think I'm missing something otherwise..

Yes, I think you haven't looked closely enough at the original data. It is a time, not a decimal number.

I have in Cell A2 132:23 hours,

 

[Sharpefiction]

Ignore me! Sorry... Must just be one of those days

Thank you for clarifying Peter

But are we not saying that 132.23 is:

One hundred and thirty-two hours and 23 minutes? Therefore, you wouldn't need to consider the 23 as a fraction of 60 for the calculation!?

Might be having a bald moment, but think I'm missing something otherwise..