Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and time calculations
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date and time calculations
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 "Pammy" wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time Calculations | Excel Worksheet Functions | |||
Date and Time Calculations | Excel Discussion (Misc queries) | |||
Date/Time Calculations | Excel Worksheet Functions | |||
Time and date calculations | Excel Worksheet Functions | |||
Date and time calculations | Excel Discussion (Misc queries) |