NetworkDays

[Guest]

I'm trying to duplicate a formula that I'm using in Excel to Access. Here is
the formula I have in excel:

=IF(J4802>=I4802,NETWORKDAYS(I4802,J4802)-1,NETWORKDAYS(I4802,J4802)+1)

Column J is my ShipDate & column I is my DueDate.

Here is what I'm using in my Access query:

IIf([ShipDate]>=[DueDate],WorkingDays([DueDate],[ShipDate])-1,WorkingDays([DueDate],[ShipDate])+1)

I'm using the function found here:

http://www.mvps.org/access/datetime/date0006.htm

To test this I'm using 08/07/07 as my due date and 08/06/07 as my ship date.
When I calculate this in Excel I get a variance of -1. When I calculate this
in Access I get 1.

Can anyone shed some light on why the difference between the two?
The -1 is the correct variance since I want to get the variance of when an
order ships as opposed to when it was due.

 

[Arvin Meyer [MVP]]

Since I wrote those functions, I guess I am the right one to answer. There
is always a positive number, if you reverse the start and end days you'll
get a zero. If you think about it there is no way that an order could be
delivered before it was shipped. You could force a negative by multiplying
by -1.

 

[Guest]

I agree with you but wouldn't there be a negative number if the ship date was
earlier than the due date? That would tell me that the order was shipped
early if you compare it to the due date. So wouldn't that be a negative
number?

Since I wrote those functions, I guess I am the right one to answer. There
is always a positive number, if you reverse the start and end days you'll
get a zero. If you think about it there is no way that an order could be
delivered before it was shipped. You could force a negative by multiplying
by -1.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm trying to duplicate a formula that I'm using in Excel to Access. Here
is
the formula I have in excel:

=IF(J4802>=I4802,NETWORKDAYS(I4802,J4802)-1,NETWORKDAYS(I4802,J4802)+1)

Column J is my ShipDate & column I is my DueDate.

Here is what I'm using in my Access query:

IIf([ShipDate]>=[DueDate],WorkingDays([DueDate],[ShipDate])-1,WorkingDays([DueDate],[ShipDate])+1)

I'm using the function found here:

http://www.mvps.org/access/datetime/date0006.htm

To test this I'm using 08/07/07 as my due date and 08/06/07 as my ship
date.
When I calculate this in Excel I get a variance of -1. When I calculate
this
in Access I get 1.

Can anyone shed some light on why the difference between the two?
The -1 is the correct variance since I want to get the variance of when an
order ships as opposed to when it was due.

 

[Arvin Meyer [MVP]]

That's the way I wrote the function. It was a while ago, but I remember
struggling with part of the code for almost a day. The number of working
days between any 2 dates must always be a positive number. We are counting
days, not trying to get the difference between 2 dates.

Of course you're welcome to change it any way you want. Start with the
DateDiff() function, if you are looking to find the difference between 2
dates.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I agree with you but wouldn't there be a negative number if the ship date
was
earlier than the due date? That would tell me that the order was shipped
early if you compare it to the due date. So wouldn't that be a negative
number?

Since I wrote those functions, I guess I am the right one to answer.
There
is always a positive number, if you reverse the start and end days you'll
get a zero. If you think about it there is no way that an order could be
delivered before it was shipped. You could force a negative by
multiplying
by -1.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I'm trying to duplicate a formula that I'm using in Excel to Access.
Here
is
the formula I have in excel:

=IF(J4802>=I4802,NETWORKDAYS(I4802,J4802)-1,NETWORKDAYS(I4802,J4802)+1)

Column J is my ShipDate & column I is my DueDate.

Here is what I'm using in my Access query:

IIf([ShipDate]>=[DueDate],WorkingDays([DueDate],[ShipDate])-1,WorkingDays([DueDate],[ShipDate])+1)

I'm using the function found here:

http://www.mvps.org/access/datetime/date0006.htm

To test this I'm using 08/07/07 as my due date and 08/06/07 as my ship
date.
When I calculate this in Excel I get a variance of -1. When I calculate
this
in Access I get 1.

Can anyone shed some light on why the difference between the two?
The -1 is the correct variance since I want to get the variance of when
an
order ships as opposed to when it was due.