Misleading Networkdays Function??

[saurabhbiswas123]

Dear All,
I want to capture the Actual Working days between Project Start & Finish Date(excluding SUNDAY only as weekly off day).
In cell A1 I have got the Project Start Date(in MMDDYYYY format) and in cell B1 I have got the Project finish date(in MMDDYYYY format).
In cell C1 I am using the following formula:-
=Networkdays(a1,b1)
But it is giving me misleading no of days since it deducts both Saturday & Sunday.
I want a formula in cell c1 which can give the actual no of days between start & finish date by deducting Sunday only.
Pls help.
Thanks
Saurabh Biswas

 

[Krishnakumar]

Hi,

There may be simple ways..

=NETWORKDAYS(A1,B1)+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))

 

[PA HS Teacher]

Barry may show up with a clever formula based on the weekday of the starting day and ending day that is less computationally intensive, but... here's what I came up with.

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX($IV:$IV,A1):INDEX($IV:$IV,B1)))<>1)) Which counts the starting date and ending date.

If you do not wish to count the starting date, ending date, or both. Subtract 1 or 2 from the above formula.

 

[saurabhbiswas123]

Hi All,
This is really working!!
What a relief....Thanks a ton for your input...
Thanks
Saurabh Biswas

 

[barry houdini]

You could also use

=B1-A1-INT((B1-A1-WEEKDAY(B1)+1)/7)

 

[jim may]

Kris - Your Formula is a REAL Keeper...
but wouldn't last numeric be 7 (for Saturday), versus 1 (for Sunday)?

 

[Krishnakumar]

Jim,

Yes, you are right. It should be 7.