Detention Clock calculation

[DK_007]

The Detention Clock calculation is as follows:

-Detention Clock starts on the Day of Delivery(Col D) and ends on the Unload Date( Col E). If the delta between the 2 dates is greater than the Free days mention below calculate how many days over.


- if the Ship day(Col B) and delivery day(Col D) are the same there are 4 free days. If not there are 3 free days( Free Days)


-The tricky part is below- Solve for the Last Free Day ( Col G)


- No detention on the 1st weekend after delivery. If a shipment delivers on the weekend, that is the 1st weekend.


Days of Detention is Last Free Day(Col G) - Unload Date( col E)



Use as many helper cells as you need please.

 

[DK_007]

Is there anything I need to clarify with my workbook or description?

 

[Nebu]

Hi:
You descriptions are little confusing could you give a manual work out of your desired output?

Thanks

 

[David Evans]

Here's my first crack at it, DK .... I'm not certain I understand how you want to handle weekend days, but I've given you a few columns in the Excel Table that tell you whether or not the Delivery Day is a Workday, and the actual delivery day

Once I'm clearer on your calculation I can consolidate the columns in to a single one

 

[DK_007]

I've added the manual calculations around orders 311- 323 , thanks for your help

 

[David Evans]

Explain how you arrive at the Last Free Day in a few cases - be sure to include one that has a weekend involved as that is the exception, if I'm understanding you correctly so far ...

I may not be able to help you immediately today as I'm heading into LA for a day's golf, but someone will be along shortly to assist I'm sure .....