Date and time calculations

P

Pammy

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,
 
T

Toppers

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
 

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