No of weekdays excluding Monday & Thursday

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
991
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:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

lapta301

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,626

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

lapta301

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

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

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