NETWORKDAYS: using correct funtion?

[AFJr]

Hi,

Objective:
A work order release is received and I enter that date into cell B8.
In cell D5 resides the due date an item is due.
In cell C8 I want to calculate the days to the due date (this works). If the
date entered is later than the due date, I need to show the days late (this
appears to work except I need to show it as a negative number) and that is my
problem.

This is the formula I'm using, it resides in cell C8:

=IF(B8=0,"",(NETWORKDAYS($D$5,B8)))

Am I using the right function? If not, can someone point me in the right
direction because I am pretty much lost.

 

[Mike H]

Maybe:-

=IF(AND(B8=0,B8>D5),(NETWORKDAYS(B8,$D$5)),NETWORKDAYS($D$5,B8))

Mike

 

[AFJr]

Hi Mike,

Thanks for trying but that did not give me the desired result. I've tried
multiplying the result using -1 in order to get a negative number if the
release date is later than the due date but that did not work either.

 

[Mike H]

The do it the other way around

=IF(AND(B8=0,B8>D5),(NETWORKDAYS($D$5,B8)),NETWORKDAYS(B8,$D$5))

Mike

 

[Ron Rosenfeld]

Hi,

Objective:
A work order release is received and I enter that date into cell B8.
In cell D5 resides the due date an item is due.
In cell C8 I want to calculate the days to the due date (this works). If the
date entered is later than the due date, I need to show the days late (this
appears to work except I need to show it as a negative number) and that is my
problem.

This is the formula I'm using, it resides in cell C8:

=IF(B8=0,"",(NETWORKDAYS($D$5,B8)))

Am I using the right function? If not, can someone point me in the right
direction because I am pretty much lost.

It's helpful if you give the results you are getting, along with sample inputs,
actual outputs and desired outputs. But as a rough guess


C8: =IF(B8=0,"",NETWORKDAYS(B8,$D$5))

B8: 10-Jan-07
D5: 15-Jan-07
C8: --> 4

B8: 30-Jan-07
D5: 15-Jan-07
C8: --> -12


--ron

 

[AFJr]

I went back to a previous document that was already filed by the quality
department. Without changing anything on the form and just playing with the
dates I got the desired result - negative - when the release date was later
than the due date. I was STUNNED! I modidfied the formula (below) to give me
a blank field in C8 if A8 is blank and to make C8=0 if the two dates were the
same.

I have no idea why it works in one doc but not the other. I compared the two
side by side scratching my head because they were exactly the same.

eventual solution:
=IF(A8=0,"",IF(A8=$C$5,0,NETWORKDAYS(A8,$C$5)))

C5 is Due Date
A8 is Released Date
C8 is result in days (+/-)

Senario: released date prior to due date:
C5 = 1-jan-07
A8 = 12-dec-06
C8 = 1

Senario: released date later than due date:
C5 = 1-jan-07
A8 = 2-jan=07
C8 = -1

Senario: released date equals due date:
C5 = 1-jan-07
A8 = 1-jan-07
C8 = 0


Thanks for everyones help who responded. Any idea why the docs with the
identical formulas wouldn't work the same?

AFJr

 

[Ron Rosenfeld]

eventual solution:
=IF(A8=0,"",IF(A8=$C$5,0,NETWORKDAYS(A8,$C$5)))

C5 is Due Date
A8 is Released Date
C8 is result in days (+/-)

Senario: released date prior to due date:
C5 = 1-jan-07
A8 = 12-dec-06
C8 = 1

Senario: released date later than due date:
C5 = 1-jan-07
A8 = 2-jan=07
C8 = -1

Senario: released date equals due date:
C5 = 1-jan-07
A8 = 1-jan-07
C8 = 0


Thanks for everyones help who responded. Any idea why the docs with the
identical formulas wouldn't work the same?

That's often a problem with data, but if you post the errors you are seeing ...

Your first example, above, doesn't make sense as there are about 15 workdays
between 12/12/06 and 1-jan-07 (without the holidays argument).

Also, NETWORKDAYS always includes the first and last day, so if that's not what
you want, then you will need to subtract or add one from all of your results.

Awaiting clarification ...
--ron

 

[Ron Rosenfeld]

Also, NETWORKDAYS always includes the first and last day, so if that's not what
you want, then you will need to subtract or add one from all of your results.

Here is a formula that adjusts the result to give a difference exclusive of the
starting date, as would result from "normal" subtraction/addition:

=NETWORKDAYS(ReleasedDate,DueDate)+IF(ReleasedDate<=DueDate,-1,1)
--ron