Elapsed Time in hours and minutes multiply by different rates

[kremlin1313]

	A	B	C	D	E
1	COMPUTER NAME <tbody> </tbody>	TIME STARTED <tbody> </tbody>	TIME FINISHED <tbody> </tbody>	Duration	Amount
2	A	07:45:00 AM <tbody> </tbody>	10:00:00 AM <tbody> </tbody>
3	B
4	C

<tbody>
</tbody>

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)