This is to count how many days there are in a month without counting Sunday and bank holidays but if public holidays are Sunday, don’t take them in account. At the end, if I already exclude Sunday and if a bank holiday falls in Sunday, I don’t have to remove it. This formula can be used for any days of the week like Saturday, Tuesday, etc. or a combination of them so it is not only for Sunday.

 

When I use the formula ?

For instance, to know how many days a shop is open during a month excluding only Sunday and bank holiday.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How is the formula ?

=TEXT()

=NETWORKDAYS.INTL()

=COUNTIFS()

This is the formula:

=NETWORKDAYS.INTL(A2,B2,"0000001")-COUNTIFS(A6:A10,">="&A2,A6:A10,"<="&B2)+COUNTIFS(A6:A10,">="&A2,A6:A10,"<="&B2,B6:B10,"Sunday")

With just the start and end date of the month, it is not possible to get the result, so before to get it, I have some works to do. Let´s start from the beginning, I have only those data, the start and end day of the month and a list of bank holidays of the year.

The first thing I will do, it is to know in the public holiday list which days fall into Sunday, for that, I will put in the cell B6 this formula:

=TEXT(A6,"dddd")

Then copy it below to the rest of the list.

Once done, I need to know how many days I have during this month without Sunday so in the cell C2:

=NETWORKDAYS.INTL(A2,B2,"0000001")

And I will change the cell format into “general”.

The next thing is to know how many days of the bank holiday list fall into the month so in C6:

=COUNTIFS(A6:A10,">="&A2,A6:A10,"<="&B2)

And amongst them, how many are “Sunday”, in C8:

=COUNTIFS(A6:A10,">="&A2,A6:A10,"<="&B2,B6:B10,"Sunday")

Now by combining those 3 formulas, I will get the result. If you need to count for other specific week day like Tuesday or Friday or etc., read my article different ways to calculate a calendar with or without bank holiday with a formula in an excel report to know how to change Sunday.