Calculate number of working days excluding a week day and public holidays with a formula in an excel report

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.

formula excel


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 ?




This is the formula:


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.

formula excel

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:


Then copy it below to the rest of the list.

formula excel

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


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

formula excel

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


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


formula excel

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.

formula excel

Interesting Management