NETWORKDAYS - what am I doing wrong?

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20
Hi folks,
I seem to be having an issue with NETWORKDAYS not doing what I want it to, and am hoping someone can tell me where exactly it is that I may be going wrong.
As per sample provided, I simply want the number of days between the two dates in 'A' (date of issue), and 'B' (actual date of departure). Sometimes the two dates are the same, hence the
time code as well as date, and sometimes the issue date is AFTER the departure date, Which is the KPI we're trying to track and improve.


I started with this standard NETWORKDAYS formula:


=NETWORKDAYS(B2,A2)-1-MOD(B2,1)+MOD(A2,1))


but was getting some wierd results, so I changed to:


=IF(A2>B2,NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1),NETWORKDAYS(B2,A2)-1-MOD(B2,1)+MOD(A2,1))

This fix appeared to work initially, but when I got to row 5, it got all screwy again,
e.g row 8 is telling me there are 3.56 days between the 2nd and 3rd of November, (Thu to Fri)??.

Excel 2010
ABC
1BPA date and TimeETD Date and TimeDays Ex W/Ends
201/11/17 11:30:0001/11/17 03:06:00-0.35
330/10/17 11:16:0001/11/17 03:06:001.66
424/10/17 12:37:0003/11/17 22:57:008.43
506/11/17 10:33:0003/11/17 22:57:00-2.48
602/11/17 08:45:0003/11/17 22:57:00-3.59
706/11/17 12:33:0003/11/17 22:57:00-2.57
802/11/17 09:24:0003/11/17 22:57:00-3.56
901/11/17 11:59:0003/11/17 22:57:00-4.46
1002/11/17 08:43:0003/11/17 22:57:00-3.59

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
C2=IF(A2>B2,NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1),NETWORKDAYS(B2,A2)-1-MOD(B2,1)+MOD(A2,1))
C3=IF(A3>B3,NETWORKDAYS(A3,B3)-1-MOD(A3,1)+MOD(B3,1),NETWORKDAYS(B3,A3)-1-MOD(B3,1)+MOD(A3,1))
C4=IF(A4>B4,NETWORKDAYS(A4,B4)-1-MOD(A4,1)+MOD(B4,1),NETWORKDAYS(B4,A4)-1-MOD(B4,1)+MOD(A4,1))
C5=IF(A5>B5,NETWORKDAYS(A5,B5)-1-MOD(A5,1)+MOD(B5,1),NETWORKDAYS(B5,A5)-1-MOD(B5,1)+MOD(A5,1))
C6=IF(A6>B6,NETWORKDAYS(A6,B6)-1-MOD(A6,1)+MOD(B6,1),NETWORKDAYS(B6,A6)-1-MOD(B6,1)+MOD(A6,1))
C7=IF(A7>B7,NETWORKDAYS(A7,B7)-1-MOD(A7,1)+MOD(B7,1),NETWORKDAYS(B7,A7)-1-MOD(B7,1)+MOD(A7,1))
C8=IF(A8>B8,NETWORKDAYS(A8,B8)-1-MOD(A8,1)+MOD(B8,1),NETWORKDAYS(B8,A8)-1-MOD(B8,1)+MOD(A8,1))
C9=IF(A9>B9,NETWORKDAYS(A9,B9)-1-MOD(A9,1)+MOD(B9,1),NETWORKDAYS(B9,A9)-1-MOD(B9,1)+MOD(A9,1))
C10=IF(A10>B10,NETWORKDAYS(A10,B10)-1-MOD(A10,1)+MOD(B10,1),NETWORKDAYS(B10,A10)-1-MOD(B10,1)+MOD(A10,1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Am I missing an extra 'IF' statement somewhere? I'm at a loss as to what it is I'm doing wrong, and would greatly appreciate any help any one can offer.
Many thanks in advance,
Wayne T
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,913
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe this...

=IF(A2>B2,NETWORKDAYS(B2,A2)+MOD(A2-B2,1),NETWORKDAYS(A2,B2)+MOD(B2-A2,1))-1

M.
 

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20
Hi Marcelo,

Thanks for the reply, and your solution actually fixes the issues I was having with lines 5 through 10, but NOW it's giving me -3.66 and -9.34 respectively for lines C3 and C4 :). Do you think I need a combination of both formula, i.e. if A2>B2, then 'your fix', if A2<b2, 'other="" fix'?="" no="" idea="" how="" to="" make="" that="" work,="" or="" even="" if="" it="" would,="" just="" wondering.="" again,="" many="" thanks="" for="" the="" speedy="" reply="" and="" suggestion.
Cheers,
Wayne T</b2,>
 
Last edited:

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20

ADVERTISEMENT

Marcelo,

I appear to have ****ed up the first 2 replies, so I'll try again, sorry. As I said, your solution fixed some issues, but then UN-fixed some that weren't :). To finish my sentence, do you think I might need to try a combo of both formula? And is that even possible?

Cheers,
Wayne T
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,913
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi Marcelo,

Thanks for the reply, and your solution actually fixes the issues I was having with lines 5 through 10, but NOW it's giving me -3.66 and -9.34 respectively for lines C3 and C4 :). Do you think I need a combination of both formula, i.e. if A2>B2, then 'your fix', if A2<b2, 'other="" fix'?="" no="" idea="" how="" to="" make="" that="" work,="" or="" even="" if="" it="" would,="" just="" wondering.="" again,="" many="" thanks="" for="" the="" speedy="" reply="" and="" suggestion.
Cheers,
Wayne T</b2,>

It worked for me

A
B
C
1
BPA date and Time​
ETD Date and Time​
Days Ex W/Ends​
2
01/11/2017 11:30​
01/11/2017 03:06​
0,35​
3
30/10/2017 11:16​
01/11/2017 03:06​
2,659722222​
4
24/10/2017 12:37​
03/11/2017 22:57​
8,430555556​
5
06/11/2017 10:33​
03/11/2017 22:57​
1,483333333​
6
02/11/2017 08:45​
03/11/2017 22:57​
1,591666667​
7
06/11/2017 12:33​
03/11/2017 22:57​
1,566666667​
8
02/11/2017 09:24​
03/11/2017 22:57​
1,564583333​
9
01/11/2017 11:59​
03/11/2017 22:57​
2,456944444​
10
02/11/2017 08:43​
03/11/2017 22:57​
1,593055556​

Formula in C2 copied down
=IF(A2>B2,NETWORKDAYS(B2,A2)+MOD(A2-B2,1),NETWORKDAYS(A2,B2)+MOD(B2-A2,1))-1

M.
 

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20

ADVERTISEMENT

OK, now I'm TOTALLY confused. I'm STILL getting this :eek::

Excel 2010
C
20.35
3-3.659722
4-9.430556
51.4833333
61.5916667
71.5666667
81.5645833
92.4569444
101.5930556

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
C2=IF(A2>B2,NETWORKDAYS(B2,A2)+MOD(A2-B2,1),NETWORKDAYS(A2,B2)+MOD(B2-A2,1))-1
C3=IF(A3>B3,NETWORKDAYS(B3,A3)+MOD(A3-B3,1),NETWORKDAYS(A3,B3)+MOD(B3-A3,1))-1
C4=IF(A4>B4,NETWORKDAYS(B4,A4)+MOD(A4-B4,1),NETWORKDAYS(A4,B4)+MOD(B4-A4,1))-1
C5=IF(A5>B5,NETWORKDAYS(B5,A5)+MOD(A5-B5,1),NETWORKDAYS(A5,B5)+MOD(B5-A5,1))-1
C6=IF(A6>B6,NETWORKDAYS(B6,A6)+MOD(A6-B6,1),NETWORKDAYS(A6,B6)+MOD(B6-A6,1))-1
C7=IF(A7>B7,NETWORKDAYS(B7,A7)+MOD(A7-B7,1),NETWORKDAYS(A7,B7)+MOD(B7-A7,1))-1
C8=IF(A8>B8,NETWORKDAYS(B8,A8)+MOD(A8-B8,1),NETWORKDAYS(A8,B8)+MOD(B8-A8,1))-1
C9=IF(A9>B9,NETWORKDAYS(B9,A9)+MOD(A9-B9,1),NETWORKDAYS(A9,B9)+MOD(B9-A9,1))-1
C10=IF(A10>B10,NETWORKDAYS(B10,A10)+MOD(A10-B10,1),NETWORKDAYS(A10,B10)+MOD(B10-A10,1))-1

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Have checked formatting, (and changed it to a couple different things), same results....WTF?? That's just insane....
 

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20
Marcelo,

Decided to start afresh in a clean workbook, and ....it works!!! Thank you SOO much for the help, appreciate your efforts and your time, you have made my day :)
Wayne T
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,913
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Marcelo,

Decided to start afresh in a clean workbook, and ....it works!!! Thank you SOO much for the help, appreciate your efforts and your time, you have made my day :)
Wayne T

Wayne

hmm... it seems my formula has a problem.
See the result in C3 rounded : 2.66.I think you want 1.66

If i'm right all you need to do is to adjust your original formula like this
C2 copied down
=IF(A2>B2,NETWORKDAYS(B2,A2)-1-MOD(B2,1)+MOD(A2,1),NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1))

M.
 
Last edited:

Forum statistics

Threads
1,148,290
Messages
5,745,876
Members
423,983
Latest member
blackworx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top