formula calculate charges based on hours

P

Pammy

I have a vehicle impound spreadsheet that lists:
date-in time-in date-out time-out charge
1/8/2007 1300 1/8/07 1400 $15.00
1/8/2007 1400 1/10/07 1500 $60.00
1/8/2007 1000 1/9/07 1500 $30.00
1/8/2007 1400 1/13/07 1545 $150.00

I have a formula now:
IF(C2=A2,IF(D2<=1400,15,30),IF(D2<=1400,(C2-A2)*30,((C2-A2)+1)*30))
this formula will charge $15.00 if picked up on the same day before 14:00
hrs. After 1400 hrs, it is $30.00 per day.

I need a new formula that will be no charge if picked up within 2 hours the
day it was towed, past 2 hours $15.00 and after 26 hours another $15 will be
charged and after 26 hours another $15 etc.
 
T

Toppers

try:

=IF(((C2+D2)-(A2+B2))*24<2,0,(INT(((C2+D2)-(A2+B2))*24/26)+1)*15)

This asumes the dates/times are in DATE/TIME formats i.e 1400 is 14:00

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top