No of weekdays excluding Monday & Thursday

[lapta301]

Hi

I have a start date in C11 of say 1st January 2017 and an end date in D11 of 31st January 2017 (It won't always be this simple) and I need to know how many normal week days there are during the period including both dates but excluding Monday's & Thursday's. The answer should be 13. I think!

As ever your help would be most appreciated.

 

[Michael M]

Have a look at Richard Schollars solution here.

http://www.mrexcel.com/forum/excel-...days-date-range-while-excluding-holidays.html

 

[Eric W]

Try:

=NETWORKDAYS.INTL(C11-1,D11,"1100101")

 

[lapta301]

Eric

Thank you for showing an interest in my problem.

Using the calendar months of the year as a simple example this is what I get with some of the months wrong. Unless I am having a senior moment.
In essence I need to count the number of Tues, Wed & Fri during the month.


Start End Formula Actual
01/01/2017 31/01/2017 13 13
01/02/2017 28/02/2017 12 12
01/03/2017 31/03/2017 14 14
01/04/2017 30/04/2017 13 12
01/05/2017 31/05/2017 13 14
01/06/2017 30/06/2017 14 13
01/07/2017 31/07/2017 13 12
01/08/2017 31/08/2017 14 14
01/09/2017 30/09/2017 14 13
01/10/2017 31/10/2017 13 13
01/11/2017 30/11/2017 14 13
01/12/2017 31/12/2017 14 13

 

[Eric W]

Originally I came up with this gruesome array formula:

=SUM(IF(ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(C11&":"&D11))),{3,4,6},0)),1))
confirmed with Control+Shift+Enter,

which correctly gives the answer for all the examples above. Then I remembered the NETWORKDAYS.INTL function that might be simpler. The problem there was that it counts the difference between 2 days, excluding any "weekend" days you designate. This means there could be an issue with the endpoints. I tried one fix for the endpoint problem which worked on some cases, but obviously not all. I'll try to fix the NETWORKDAYS version, but it may end up as complicated as my original formula. I'll let you know, but try the array formula for now.

 

[Dave Patton]

try
=NETWORKDAYS.INTL(A2,B2,"1001011")

or the equivalent of the Array Formula

=SUMPRODUCT(--(ISNUMBER(MATCH(WEEKDAY(ROW(INDIRECT(A2&":"&B2))),{3,4,6},0))))

 

[Eric W]

try
=NETWORKDAYS.INTL(A2,B2,"1001011")

I suppose reading the manual better might help!

 

[lapta301]

Dave and Eric thank you so much it seems to work a treat.

Along with Michael I am very grateful to you all for taking an interest and time in resolving my question.