NETWORKDAYS - what am I doing wrong?

[Wayne T]

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

	A	B	C
BPA date and Time	ETD Date and Time	Days Ex W/Ends
01/11/17 11:30:00	01/11/17 03:06:00
30/10/17 11:16:00	01/11/17 03:06:00
24/10/17 12:37:00	03/11/17 22:57:00
06/11/17 10:33:00	03/11/17 22:57:00
02/11/17 08:45:00	03/11/17 22:57:00
06/11/17 12:33:00	03/11/17 22:57:00
02/11/17 09:24:00	03/11/17 22:57:00
01/11/17 11:59:00	03/11/17 22:57:00
02/11/17 08:43:00	03/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

 

[Wayne T]

Marcelo,

Yep, missed that one, thank you again. I have NO idea what's going on in the original worksheet, but just to see what would happen, I copied and pasted the dates in C3 from your previously posted spread sheet example, and **** me, the formula worked. Ditto for C4. ALL the cells in my original spreadsheet are formatted EXACTLY the same way for each column, at a total loss as to why I keep getting the wrong result just in those two cases.

Thank you again for the updated solution. Enjoy the rest of your day/week
Cheers,
WT