No of weekdays excluding Monday & Thursday

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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.
 
Upvote 0
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))))
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top