Excel Weekends Excle 2007 3

[rose12346]

I have a very large worksheet which contains data related to shipping customer orders on time. I have the Order date (date customer ordered), create date (date the warehouse received the order), ship date (date the order shipped from the warehouse) and date delivered (date delivered by the carrier). What I am trying to calculate is the number of days from create date to ship date and also ship date to deliver date. I would like to exclude weekends and holidays but if my create date is a weekend date then I noticed that Excel counts that as a day even though it is a weekend date, for example create date Saturday 1/17/2015 because this is a start date it counts this a one day. How do I exclude the weekend days from being counted if they are the create date? I would also like to know if there is a way to define that there are times that weekends or certain dates need to be included because we worked weekends. Finally depending upon how the order was shipped there are expected turn times for our carrier to deliver the package but certain shipping methods i.e.; 2 day air if shipped on a Friday needs to exclude Sat and Sun from the calculation of the turn time. Sure could use some help on how to calculate these differences and here are some examples:
Processing time Create date 11/1/2014 (Sat) ship date 11/3/2014 processing time should be zero days because 11//1 and 11/2 were weekend days not worked and 11/3 is the first time we could have processed the units and we ship that date so there we meet the expectation. If the ship date were to be 11/4 we would have been changed 1 day processing. Note this is based upon a ground shipment.
Delivery time: Create date 11/06/2014 ship date 11/6/2014 2nd day air delivery date expected to be 11/10/2014 because 11/8/2014 was a Sat day and this was not shipped Saturday delivery so Sat and Sun would need to be excluded in the calculation of the delivery date.

I appreciate any help that can be provided.

 

[Andrzejek]

Since you introduce same rules (exclude weekends) and then introduce some exceptions to your rule (but include weekends when we work weekends), you will need to keep somewhere the schedule of your work. You can also keep in it your holidays.

I would guess that simple formulas in Excel will become very complicated and really, really though to read and maintain pretty fast. I would suggest to go with VBA, or better yet – an app in Access or other data base.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

I have worked for several aerospace companies over the past 30 years. The shop often worked weekends and holidays.

We NEVER used functions to figure work days!!!

The ONLY way to assure that EVERYONE is on the same calendar page, is to generate, maintain & use a workday calendar. Typically this Table includes calendar dates, work days, accounting days.

Anything else is open to ambiguity.

 

[zelgar]

The NETWORKDAYS function will calculate the number of days, excluding weekends and holidays you list from being calculated. There's also a NETWORKDAYS.INTL function that will let you change the change the "weekend" days.