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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
423
dshtesy
D
C
  • Question Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
769
Scott Huish
Scott Huish
V
  • Question Question
Hours functions
Replies
4
Views
569
jkpieterse
jkpieterse
A
  • Question Question
Turn Around Time with Set Break Times
Replies
3
Views
548
azhrei
A
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

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