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
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><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
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 | |
|---|---|---|---|
| 1 | BPA date and Time | ETD Date and Time | Days Ex W/Ends |
| 2 | 01/11/17 11:30:00 | 01/11/17 03:06:00 | -0.35 |
| 3 | 30/10/17 11:16:00 | 01/11/17 03:06:00 | 1.66 |
| 4 | 24/10/17 12:37:00 | 03/11/17 22:57:00 | 8.43 |
| 5 | 06/11/17 10:33:00 | 03/11/17 22:57:00 | -2.48 |
| 6 | 02/11/17 08:45:00 | 03/11/17 22:57:00 | -3.59 |
| 7 | 06/11/17 12:33:00 | 03/11/17 22:57:00 | -2.57 |
| 8 | 02/11/17 09:24:00 | 03/11/17 22:57:00 | -3.56 |
| 9 | 01/11/17 11:59:00 | 03/11/17 22:57:00 | -4.46 |
| 10 | 02/11/17 08:43:00 | 03/11/17 22:57:00 | -3.59 |
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
Worksheet Formulas
<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
. 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.
