NETWORKDAYS Function

[Justinian]

I am using NETWORKDAYS to calculate the difference between two work dates and am having a frustrating problem. In cell E4 is 11/20 and in cell D4 is 11/21. The difference between those two days should be 1 but Excel is giving me 3. Why?

 

[barry houdini]

Re: NETWORKDAYS Function Help

3, Are you sure? NETWORKDAYS counts both start and end date so I'd expect the answer to be 2. As long as start and end dates will both be working days you can just subtract 1 from the result to give you the difference in working days

 

[Justinian]

Re: NETWORKDAYS Function Help

That is what I did (use -1) and I just tested on a fresh spreadsheet.

If you put 11/20 in A1 and 11/21 in B1, =NETWORKDAYS(A1,B1)-1 in cell C1 returns 1, which is correct. Switch the two dates (put 11/21 in A1 and 11/20 in B1) and you will see it returns a three all of a sudden.

 

[barry houdini]

Re: NETWORKDAYS Function Help

In that case it will return -3

With NETWORKDAYS if the first date (in the formula) is later than the second date you get a negative value, in this case -2 then you are subtracting a further 1 so you get -3

Is it valid for you to have negative results? If so then you probably need to add 1, not subtract, in that scenario. You can automate that with this formula

=NETWORKDAYS(A1,B1)-SIGN(NETWORKDAYS(A1,B1))

If you use that formula then you get 1 for the dates in order or -1 for the dates reversed
​

 

[Justinian]

Re: NETWORKDAYS Function Help

This is the formula I am currently using and yes, I want both positive and negative values:

=IF(A1=FALSE,"",IF(AND(NOT(ISBLANK(D1)),E1=FALSE),"IP",NETWORKDAYS(D1,E1)-1))

So if A1 is 11/20 and B1 is 11/21, I want to return 1; if B1 is 11/20 and A1 is 11/21, I want to return -1

 

[barry houdini]

Re: NETWORKDAYS Function Help

You can change that like this

=IF(A1=FALSE,"",IF(AND(NOT(ISBLANK(D1)),E1=FALSE),"IP",NETWORKDAYS(D1,E1)-SIGN(NETWORKDAYS(D1,E1))))

 

[Justinian]

Re: NETWORKDAYS Function Help

Awesome, that works! Thank you.