value for weekends

[mateo_feo]

Hello all
Working in transportation, and I have a customer who judges our transit time performance as:

Date of Pickup = 0
Weekends count as 1 day

so, when calculating in excel between 2 dates, i need a formula that will return a value of each date between pickup date and deliver date(minus 1 for day zero) and plus 1 for each weekend between the 2 dates.

Hope i wasnt confusing. Can anyone help?

 

[shades]

Howdy and welcome to the board. Can you post a small sample (dummy data). That way any solution will tie into what you have already.
________
Buy solo vaporizer

 

[mateo_feo]

Cell A2 is Picked Date
Cell B2 is Delivered Date
The formula for C2 should show the value between the 2 dates, inclusive of weekends(valued at 1 day) and minus the Picked Date.

Delivered Date minus Picked Date gives a value. That value must have 1 subtracted from it(because the Picked Date is valued as day 0)

The result is the amount of days between.

Weekends count only as 1 day, so each weekend that falls between the Picked Date and Delivered Date has to be considered 1 day.
So if a Saturday falls between the dates, then it is considered 1 day, and if a full weekend falls between the dates, it still is considered 1 date. I hope this helps, and thanks for working with it.

 

[shades]

Okay, try this in cell C3

NETWORKDAYS(A2,B2)+ROUND(((B2-A2)-NETWORKDAYS(A2,B2))/2,0)

(You may need to add the add-in Analysis Toolpak to see this NETWORKDAYS function)

This may be simplified, but it would help to check whether it will meet your needs.
________
Herbal vaporizers

 

[shades]

Note, this does not handle holidays. If you have holiday schedule for the year, then we can add a third argument to the NETWORKDAYS formula to look in the range of holidays.
________
Honda Civic Gx

 

[mateo_feo]

anomoly in the above formula

Hi Shades
I was looking at this in detail and found one example where it doesnt work

PKD -8/31/07
DLV - 10/24/07

I count 46 days, and yet the formula results in 47 days... can you figure out the reason, im totally baffled.

 

[shades]

Actually I think it is off by 1 in all examples. So, put -1 at the end of the formula.
________
HERBAL VAPORIZER

 

[mateo_feo]

not to be a pest, so it should look like this:

NETWORKDAYS(A2,B2)+ROUND(((B2-A2)-NETWORKDAYS(A2,B2))/2,0)-1

if so, then that doesnt work with 7-30 to 8-1 (which should be 2, but results in 1).

Someone suggested:
=(NETWORKDAYS(A2,B2)-1)+INT((WEEKDAY(A2)+B2-A2)/7)
Which seems to work, what do you think..

 

[shades]

Whatever works.
________
Bandit Series