mateo_feo
11-13-2007, 11:26 AM
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
11-13-2007, 11:56 AM
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.
mateo_feo
11-13-2007, 12:08 PM
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
11-13-2007, 12:37 PM
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.
shades
11-13-2007, 12:39 PM
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.
mateo_feo
11-14-2007, 01:33 PM
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
11-14-2007, 02:25 PM
Actually I think it is off by 1 in all examples. So, put -1 at the end of the formula.
mateo_feo
11-14-2007, 03:15 PM
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..