Hi, issue with days between dates

[Sabrina19]

Hello there,

It seems I am getting a weird change in days when using the following:

Explanation.xlsx

What it basically comes to, I have 3 dates, whereby I calculate the ''=networkdays'' (I assume those are working days minus Saturday/Sunday) I am getting an extra day with the first line of dates.

*What I have thought about myself: perhaps due to the fact that the dates include data which is in a weekend (19-10-2014) this could possibly mean an error? the weird thing however is that the second line of dates also include one date in a weekend (14-10-2014)

Could someone please help me, much appreciated!

-Sabrina

[Ron Coderre]

The formulas in your posted workbook are returning corrct values.
To verify...
• List the dates from 13-Oct through 14-Nov.
• Format those dates to see the date and day (Example: Monday, October 13, 2014)
• Then check the calculations.

Does that help?

[ChemistB]

NetWorkDays always includes the first day as 1 (provided it's a workday)
so NetWorkDays(11/21/2014, 11/21/2014) = 1
NetWORKDAYS(11/20/2014, 11/21/2014) = 2 even though there's 1 day difference. If you don't want to start counting from the first date, subtract 1
=NETWORKDAYS(11/20/2014, 11/21/2014)-1

Any weekend days count as 0 so
=NETWORKDAYS(11/22/2014 (Saturday), 11/23/2014 (Sunday)) = 0
Did that help?

[Sabrina19]

Thank you so much for your replies both Ron and chemist!

I'm trying to make sense out of it, however, Chemist, what would be the correct formula for both these lines of dates if I only want to know the difference between them (excluding the weekends)

[ChemistB]

For example, E6 would be

=NETWORKDAYS(B6,D6)-1
If you use that formula throughout, do you have any that do not give you what you would expect?

[Sabrina19]

Yes, that fixes the first one, but I cannot do that for the second one (changes the dates)

I am looking for a sustainable formula which will work on let's say, 3000/4000 dates, which includes both the situation with the first and second date.

Would there be such thing?

Thank you so much for your help!

[ChemistB]

I'm not following. In which column could you not do that?

[Sabrina19]

Sorry, trying to do my best to get my head around this.

I actually does not fix anything, I will try to be a little more clear:

Order + POD = 24
------------------------
Tracking + POD = 5
Order + tracking = 20
_____
25 <-- this should be 24, not 25. Because remember, Order + POD = 24, it cannot be that the steps in between are able to add one day, which is currently happening.

[ChemistB]

If you correct all your NETWORKDAY Formulas to be the same then the sums will work out. See attachment.

[Sabrina19]

Thanks once again, that is also what I tried.

However, when looking order: 13/10/2014 and 19/10/14 - that is clearly 5 working days, your formula makes that 4, which is not correct.

[ChemistB]

Ahhh, lightbulb

Okay, the problem with F+G =E is that there is a day that is counted twice, the day of tracking. So you need to subtract 1 from that formula.
In your second row, the tracking day is a Sunday so it counted as 0 and 2 * 0 still equals 0 so that row works.
Will your tracking day sometimes appear on non-work days? If so, we can work out a formula that takes that into account. I assume Order days are always on workdays, how about POD days?

[Sabrina19]

Awesome!

Well actually, all the 3 dates in each line, can occur in the weekend. Both an order can made in the weekend, Tracking van be updated in the weekend, and POD's can be signed in the weekend.

I can try to upload the whole file I am working on, just dont see it so quickly where to do that.

Help is much appreciated!!

[Sabrina19]

testt.xlsx

Ok it worked, as you can see, most of my lines of date (about 99% even) have in column J a -1, which means that the time from customer order, till POD signed by customer is (for example) 25, but should be -1 to reflect 24 (which is correct) <-- sorry for being so confusing, hope it makes sense!

[ChemistB]

Okay, In G, I put this formula

=IF(WEEKDAY(B2,2)>5, D2+E2, D2+E2-1)

It seems to work from the ones I checked. In your file, I also substituted NETWORKDAYS into D and E. Towards the end you have some where the tracking date comes after the POD date which doesn't make sense to me so not sure how that would be calculated.

[Sabrina19]

Sorry, I had to go for a moment. Will test just now and let you know. Thank you so much!

[Sabrina19]

Thanks Chemist,

Maybe I forgot to say that I am OK with the point of order to POD, those with a simple =networkdays is working, however between those two dates, between the date of order and POD, I have two additional steps.

1. From the order till tracking is added to the order
2. Tracking placed to the order, order is delivered (POD)

Currently, when point of order till POD is 9 days, point 1 + point 2 will be 10 days, which is wrong, it also should be 9 days.

I am wondering, why is this not also 9 days, where is the extra day coming from?

[Sabrina19]

1. Order, till order receives tracking
2. Tracking till delivery (POD)

If from Order to POD = 10 days, If I apply point 1 + 2 I will always get 11 days, where is that extra day coming from, that is my main concern, can you help me with that?

[ChemistB]

Can you point to an example on your sheet or upload a new sheet with some examples?

[Sabrina19]

test2.xlsx

See my info in Green / Column P

Thanks!

[ChemistB]

It's not a case of which column has the extra day, it's that you are adding 1 day twice.

Simple case, Order is done on Monday, Tracking on Wednesday, POD on Friday

From Order to Tracking = Mon, Tues, Wed = 3
From Tracking to POD = Wed, Thur, Fri = 3
From Order to POD = Mon, Tue, Wed, Thur, Fri = 5

That's why my formula subtracts 1 if the tracking occurs on a weekday
=IF(WEEKDAY(B2,2)>5, D2+E2, D2+E2-1)
Does that help or am I missing the point again?

[Sabrina19]

From Order to Tracking = Mon, Tues, Wed = 3
From Tracking to POD = Wed, Thur, Fri = 3
From Order to POD = Mon, Tue, Wed, Thur, Fri = 5

That exactly, that is where I would use Wed x2

But if I use your formula, it changes the ''From Order to POD'', and takes away one day there.

What I need to know, or what is more important are:

From Order to Tracking = Mon, Tues, Wed = 3
From Tracking to POD = Wed, Thur, Fri = 3

Because that somehow should be 5 as well, it cannot be that from order to delivery = 6 days, while in real its 5 days, I'm sure you understand this even better than I do, and that is why it's so difficult for me to explain this

is it somehow possible to eliminate that extra day in:

From Order to Tracking = Mon, Tues, Wed = 3
From Tracking to POD = Wed, Thur, Fri = 3

or should I just take 0.5 days off each?

[ChemistB]

I still think the best way to handle this is, in the cell where you are doing the adding, (Column P in test2)


=IF(WEEKDAY(B5,2)>5, M5+N5, M5+N5-1)

Or you could just accept that Excel knows what it is doing and that the formula for Networkdays for Order to POD is correct

=NETWORKDAYS(A5,C5)