NETWORKDAYS - what am I doing wrong?

W

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
BPA date and TimeETD Date and TimeDays Ex W/Ends
01/11/17 11:30:0001/11/17 03:06:00
30/10/17 11:16:0001/11/17 03:06:00
24/10/17 12:37:0003/11/17 22:57:00
06/11/17 10:33:0003/11/17 22:57:00
02/11/17 08:45:0003/11/17 22:57:00
06/11/17 12:33:0003/11/17 22:57:00
02/11/17 09:24:0003/11/17 22:57:00
01/11/17 11:59:0003/11/17 22:57:00
02/11/17 08:43:0003/11/17 22:57:00

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]-0.35[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]1.66[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]8.43[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]-2.48[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]-3.59[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]-2.57[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]-3.56[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]-4.46[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]-3.59[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(A2>B2,NETWORKDAYS(A2,B2)-1-MOD(A2,1)+MOD(B2,1),NETWORKDAYS(B2,A2)-1-MOD(B2,1)+MOD(A2,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(A3>B3,NETWORKDAYS(A3,B3)-1-MOD(A3,1)+MOD(B3,1),NETWORKDAYS(B3,A3)-1-MOD(B3,1)+MOD(A3,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IF(A4>B4,NETWORKDAYS(A4,B4)-1-MOD(A4,1)+MOD(B4,1),NETWORKDAYS(B4,A4)-1-MOD(B4,1)+MOD(A4,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=IF(A5>B5,NETWORKDAYS(A5,B5)-1-MOD(A5,1)+MOD(B5,1),NETWORKDAYS(B5,A5)-1-MOD(B5,1)+MOD(A5,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=IF(A6>B6,NETWORKDAYS(A6,B6)-1-MOD(A6,1)+MOD(B6,1),NETWORKDAYS(B6,A6)-1-MOD(B6,1)+MOD(A6,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=IF(A7>B7,NETWORKDAYS(A7,B7)-1-MOD(A7,1)+MOD(B7,1),NETWORKDAYS(B7,A7)-1-MOD(B7,1)+MOD(A7,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=IF(A8>B8,NETWORKDAYS(A8,B8)-1-MOD(A8,1)+MOD(B8,1),NETWORKDAYS(B8,A8)-1-MOD(B8,1)+MOD(A8,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=IF(A9>B9,NETWORKDAYS(A9,B9)-1-MOD(A9,1)+MOD(B9,1),NETWORKDAYS(B9,A9)-1-MOD(B9,1)+MOD(A9,1))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=IF(A10>B10,NETWORKDAYS(A10,B10)-1-MOD(A10,1)+MOD(B10,1),NETWORKDAYS(B10,A10)-1-MOD(B10,1)+MOD(A10,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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:
Upvote 0
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
 
Upvote 0
Wayne T said:
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,>
Click to expand...

It worked for me

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
BPA date and Time​
[/td][td]
ETD Date and Time​
[/td][td]
Days Ex W/Ends​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
01/11/2017 11:30​
[/td][td]
01/11/2017 03:06​
[/td][td]
0,35​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
30/10/2017 11:16​
[/td][td]
01/11/2017 03:06​
[/td][td]
2,659722222​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
24/10/2017 12:37​
[/td][td]
03/11/2017 22:57​
[/td][td]
8,430555556​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
06/11/2017 10:33​
[/td][td]
03/11/2017 22:57​
[/td][td]
1,483333333​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
02/11/2017 08:45​
[/td][td]
03/11/2017 22:57​
[/td][td]
1,591666667​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
06/11/2017 12:33​
[/td][td]
03/11/2017 22:57​
[/td][td]
1,566666667​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
02/11/2017 09:24​
[/td][td]
03/11/2017 22:57​
[/td][td]
1,564583333​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
01/11/2017 11:59​
[/td][td]
03/11/2017 22:57​
[/td][td]
2,456944444​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
02/11/2017 08:43​
[/td][td]
03/11/2017 22:57​
[/td][td]
1,593055556​
[/td][/tr]
[/table]

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

M.
 
Upvote 0
OK, now I'm TOTALLY confused. I'm STILL getting this :eeek::

Excel 2010
C

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]0.35[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]-3.659722[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-9.430556[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1.4833333[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.5916667[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1.5666667[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1.5645833[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2.4569444[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1.5930556[/TD]

</tbody>
Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(A2>B2,NETWORKDAYS(B2,A2)+MOD(A2-B2,1),NETWORKDAYS(A2,B2)+MOD(B2-A2,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(A3>B3,NETWORKDAYS(B3,A3)+MOD(A3-B3,1),NETWORKDAYS(A3,B3)+MOD(B3-A3,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=IF(A4>B4,NETWORKDAYS(B4,A4)+MOD(A4-B4,1),NETWORKDAYS(A4,B4)+MOD(B4-A4,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=IF(A5>B5,NETWORKDAYS(B5,A5)+MOD(A5-B5,1),NETWORKDAYS(A5,B5)+MOD(B5-A5,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=IF(A6>B6,NETWORKDAYS(B6,A6)+MOD(A6-B6,1),NETWORKDAYS(A6,B6)+MOD(B6-A6,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=IF(A7>B7,NETWORKDAYS(B7,A7)+MOD(A7-B7,1),NETWORKDAYS(A7,B7)+MOD(B7-A7,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=IF(A8>B8,NETWORKDAYS(B8,A8)+MOD(A8-B8,1),NETWORKDAYS(A8,B8)+MOD(B8-A8,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=IF(A9>B9,NETWORKDAYS(B9,A9)+MOD(A9-B9,1),NETWORKDAYS(A9,B9)+MOD(B9-A9,1))-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=IF(A10>B10,NETWORKDAYS(B10,A10)+MOD(A10-B10,1),NETWORKDAYS(A10,B10)+MOD(B10-A10,1))-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Have checked formatting, (and changed it to a couple different things), same results....WTF?? That's just insane....
 
Upvote 0
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
 
Upvote 0
Wayne T said:
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
Click to expand...

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:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
C
  • Question Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
733
Scott Huish
Scott Huish
V
  • Question Question
Hours functions
Replies
4
Views
555
jkpieterse
jkpieterse
A
  • Question Question
Turn Around Time with Set Break Times
Replies
3
Views
544
azhrei
A
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
964
UnitedCloud01
U

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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
Back
Top