Misleading Networkdays Function??

saurabhbiswas123

Board Regular
Joined
Jul 16, 2008
Messages
71
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

There may be simple ways..

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

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

You could also use

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

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,483
Kris - Your Formula is a REAL Keeper...
but wouldn't last numeric be 7 (for Saturday), versus 1 (for Sunday)?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,202
Members
414,513
Latest member
junbuggle

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
Top