Production and Delivery 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 can be delivered north on the 23/01/2015 or south on the 24/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.

Any help will be greatly appreciated.

Kind Regards,
Mike

Since I'm assuming you don't already have a worksheet available to work on, I attached an example that could be a possibility. Note that there are named ranges and data validation lists being used on the second sheet.

Cells C2:C4 are variable. You pick the first two from a dropdown, and enter the "Called On" date in the third cell, and cell C6 calculates the day that it is due (taking into account non-working weekends only). The formula has been made scalable by referencing data on sheet 2, so if your delivery dates or build day times ever change, you can alter them on the second sheet's tables and the formula on sheet 1 will automatically update.

Hope something like this helps!

I'm a little confused now, because in your example, the 24th is a Saturday, but you are saying you don't deliver on weekends?

Either way, check the attached workbook and see if I'm in the right direction.

No worries, see addition to post above with altered workbook.

So hopefully last question before we wrap this up. I believe the previous example may still have been flawed if I am understanding this correctly. If it takes 6 days to build a kitchen, but you only work weekdays, then total it takes 8 days to build the kitchen (including weekends). That's easy enough - but does the same principle apply to delivery? If you ship a product on a friday and it is going north, what day should it arrive at it's destination if it is only supposed to take 2 days - Sunday, or Tuesday?