Date and time calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet for towed vehicles. If I have an IN Column that has a
date and time and an OUT column that has a date and time released. I also
have a column that has amounts of charges ($45.00 per day) In the third
column I would like a calculation based on the In and Out times: Example, if
a vehicle is picked up before 2 hours, there is no charge, after 2 hours the
same day, 1 day of storage is charged. If a vehicle is picked up the next
day before 1400 hrs - one day charged, but if the vehicle is picked up 1401
hrs - two days storage is charged. etc.
Is there a formula that would calculate based on the hours and days? Thanks,
 
try:

=IF(DATEDIF(A2,B2,"d")=0,IF(ROUND(MOD(B2-A2,1)*24,2)<=2,0,1),IF(TIME(HOUR(B2),MINUTE(B2),0)<=TIME(14,0,0),DATEDIF(A2,B2,"d"),DATEDIF(A2,B2,"d")+1))

to give number of days to charge

A2 = IN
B2= OUT
 
Back
Top