Production and delivery time Formula

Hi Everyone,

I need a formula to help with my companies delivery dates.

The way we work it out is.

The job is put into production and takes a set amount of days to be completed,

Kitchens= 6 days
Bedrooms= 5 Days
Bathrooms= 12 Days

on the last day of the days it arrives in the warehouse,

from here we calculate the delivery date.

2 days for our north deliveries

3 days for our south deliveries

here is an example (kitchen Called of date 14/01/2015 arrives in the warehouse 21/01/2015, is loaded on the 22/01/2015 can be delivered north on the 23/01/2015 or south on the 26/01/2015)

currently this is worked out in our heads which causes a few problems.

What I am struggling with is weekends and adding days where we don't have production. As I will need a box to add days where production is closed.

Any help will be greatly appreciated.

Kind Regards,
Mike

see the attached file

Created two tables 1 for holidays and 2nd for item wise and zone wise days required

Glad to help you and thanks for reputation