Elapsed Time in hours and minutes multiply by different rates

[kremlin1313]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="width: 118"]COMPUTER NAME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]TIME STARTED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98"]TIME FINISHED[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Duration[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]07:45:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]10:00:00 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to display in column D the elapsed time between B and C columns in hh:mm. and the corresponding rates in column E.
Conditions:
IF D2 = 1, D2*15
IF D2 > 1, multiply the number of hours to 15(the rate per hour) plus the rates if below 1 hour
example if DURATION is 1 hour and 25 minutes, its should be $15+10= $25
If D2:
< 20 minutes, $5
> 20 minutes but < 40 minutes, $10
> 40 minutes but < 1 hour, $15

 

[NoSparks]

Try this

D2 =24*(C2-B2) formatted [h]:mm

E2 =INT(D2*24)*15 + IF(MOD(D2*24,1)*60<=20,5,IF(AND(MOD(D2*24,1)*60>20,MOD(D2*24,1)*60<=40),10,15)) formatted as Currency

 

[kremlin1313]

THANK YOU VERY MUCH! I just remove the 24* in D2 and it works great.

 

[kremlin1313]

Sorry but I have problem

http://postimg.org/image/n1jgv1crb/

[/URL][/IMG]

How to solve this?


the formula in D2 is =C2-B2
the formula in E2 =INT(D2*24)*15 + IF(MOD(D2*24,1)*60<=20,5,IF(AND(MOD(D2*24,1)*60>20,MOD(D2*24,1)*60<=40),10,15))

 

[ukmikeb]

Hi

Try in E2:-

=INT(D2*24)*15+CEILING(MOD(MOD(D2,1)*1440,60),20)/20*5

hth

 

[kremlin1313]

THANK You Mike for your formula. I REALLY appreciate it but there's a problem. If the DURATION is 1 hr, it will return to $20, which is it adds $5. Also when the DURATION is 2hrs, it will return $35, which it adds again $5. It must be $15 per hour.

 

[barry houdini]

If D2:
< 20 minutes, $5
> 20 minutes but < 40 minutes, $10
> 40 minutes but < 1 hour, $15

This doesn't cover a scenario where the minutes are exactly 20 or 40. What results do you expect if D2 = 1:20 or 1:40?

If you are always rounding up to the next 20 minutes try this formula

=CEILING(D2,"0:20")*24*15

format as currency

 

[ukmikeb]

THANK You Mike for your formula. I REALLY appreciate it but there's a problem. If the DURATION is 1 hr, it will return to $20, which is it adds $5. Also when the DURATION is 2hrs, it will return $35, which it adds again $5. It must be $15 per hour.

Sorry, try this :-

=INT(MOD(D2,1)*24)*15+(((CEILING(MOD(MOD(D2,1)*1440,60),20)/20)-(INT(MOD(D2,1)*1440)/60=ROUND(MOD(D2,1)*24,2)))*5)