# NETWORKDAYS - what am I doing wrong?

#### Wayne T

##### New Member
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

</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))

</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.
Wayne T

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Marcelo Branco

##### MrExcel MVP
Maybe this...

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

M.

Mmm....

Last edited:

#### Wayne T

##### New Member
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
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
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
OK, now I'm TOTALLY confused. I'm STILL getting this :

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

</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

</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
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
You are welcome. Glad to help.

M.

#### Marcelo Branco

##### MrExcel MVP
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:

Replies
3
Views
158
Replies
6
Views
852
Replies
4
Views
270
Replies
1
Views
265
Replies
1
Views
255

1,182,023
Messages
5,933,320
Members
436,888
Latest member
Kantonides

### 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.

### Which adblocker are you using?

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

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