time calculations

[Pammy]

I have a spreadsheet for vehicles towed: there are these fields:
date-in time date-out time (the time is military time)
date-in time
date-out time
question: If I have a vehicle towed, 8/1/2007 1300 8/1/2007 1400
8/1/2007 1300
8/1/2007 1410
8/1/2007 1300
8/2/2007 1410

I need a calculation that if a vehicle is towed and picked up the same day
by 1400 a flat rate is $15.00 1/2 the day rate, but if is picked up the same
day at 1405, it is charged $30.00 (this is a days storage charge)

If it gets picked up the next day before 1400 it is charged $30.00 but if is
at 1405 then it is charged $60.00. The towing fee is $30.00 per day, and the
new day starts at 1405.

 

[Toppers]

A B C
D E

Date in Time Date-out Time Charge
01/08/2007 1300 01/08/2007 1400 £15.00
01/08/2007 1300 01/08/2007 1410 £30.00
01/08/2007 1300 02/08/2007 1410 £60.00
01/08/2007 1300 03/08/2007 1410 £90.00

in E2:


=IF(C2=A2,IF(D2<=1400,15,30),IF(D2<=1400,(C2-A2)*30,((C2-A2)+1)*30))

copy down


HTH