Oil & Gas Detention Formulas with Daily Caps

Hello, all!

I work for a company that services Oil & Gas wells with sand trucks. When drivers wait to offload their product they are paid for their time. There are boundaries, however, and I need help establishing a formula that accounts for many variables. Here is an example breakdown:

Driver's appointment is 7/14/13 @ 09:00
He arrives 7/14/13 @ 08:00
He does not leave the well until 07/16/13 @ 0130

Here are some rules we must follow:

Detention does not start until the appointment (ie: 7/14/13 @ 09:00). You cannot charge detention before then.
The driver does not start earning money from detention until after the first two hours (ie: 7/14/13 @ 11:00)
There is a cap on detention: Only 12 hours in a 24 hour period are paid. (ie: 7/14/13 11:00- 7/15/13 @ 23:00). Detention does not start up again until 24 hours after the initial appointment time has passed (ie: 7/15/13 @ 09:00). Once again, the driver gives up the first two hours (7/15/13 @ 11:00) and starts earning again at 7/15/13 @ 11:00.

If a driver arrives AFTER the appointment time then his detention clock starts from the time he got there, not the appointment time, and he STILL gives up the first two hours. The clock still restarts 24 hours after the driver arrives, not from the appointment time.

In this example the driver has accumulated 40.5 hours of detention for being on time, but is only 24 hours are billable.
If he arrived at 15:00 on 7/14/13, he would accumulate 34.5 hours of detention, but only 22.5 hours would be billable.

Does this make sense? The process, not the practice. I need a formula that gives me the total billable hours of detention based on Appointment Time, Arrival Time, and Departure time. All need dates, because these guys can sit around for a few days.

Thank you!

PickYerBrain,

Please see the attached. I could not think of a way to do this with standard Excel functions so I created a custom function called BillableDetentionTime. Code is below.

Please Login or Register  to view this content.

In your first example I agree with your total of 24 billable hours, however for the guy who arrives at 3pm I calculate he would get 20.5 billable hours. Can you check this otherwise I will need to look at the logic again.

Please put lots more examples in to check whether it works OK. If not come back to me with the examples so I can fix up.

Regards

David

You are correct, it is 20.5, not 22.5. I forgot to remove the first two hours. I will check this out and see how it works with the rest of the workbook. Thank you so much for your help!

This certainly did the trick! Thank you so much!