# Calculate the number of workdays in a given month

#### OverKnight

##### New Member
I am using the following formula to calculate the number of workdays (Monday through Friday) to date in a given month:
INT((T\$4-WEEKDAY(T\$4-S5)-DATE(YEAR(T\$4),1,-7))/7)-SUMPRODUCT((WEEKDAY(T\$5:T\$10,2)=S5)*(YEAR(T\$5:T\$10)=YEAR(T\$4))*(T\$5:T\$10<=T\$4))
I was using this formula in a worksheet I updated monthly, but now I need to be able to compare monthly to year-to-date. This formula is in P19:P23, with S5:S10 updated for each row.
• T4contains yesterday’s date (yesterday, because the data on this sheet are updated the following day).
• S5:T10 contains the legal holidays for the year.

I would like to be able to change this formula to show the number of workdays in a given month. This formula is currently returning 9, 8, 8, 8 and 8 for Monday through Friday, which is correct for the first two months of 2016. The correct values for February are 5, 4, 4, 4 and 4. I’d guess a start and end date needs to be added to this formula, but if this is the correct approach, I have no idea how to do this. I searched for this, but could not find a solution that lists each day of the week. Can anyone recommend a solution?

Thank you.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

Have you tried using the NETWORKDAYS function? It's a lot simpler for other people to understand and has the following format, with the HolidayList corresponding to your dates held in T5:T10.

Code:
``=NETWORKDAYS(StartDate,EndDate,HolidayList)``

Is a requirement of yours to have the breakdown by day, e.g. a certain number of Mondays, Tuesdays etc? NETWORKDAYS won't do this. One way you could count these would be as follows, substituting Tue, Wed, Thu and Fri for Mon as appropriate, with B2 containing the start date and B3 containing the end date (you can use =EOMONTH(B2,0) function in B3 to generate this):

Code:
``=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&B3)),"ddd")="Mon"))``

You can then subtract the holidays using the second part of your formula above.

Hope that helps

Mackers

Last edited:
I was having a hard time following what the formula was supposed to be doing.
But Mackers helped clear that up.

So you want to know how many Mondays there are, How many Tuesdays there are, etc ?

Which version of Excel are you using?

If you are using Excel 2010 or later....
With this set up:

 A​ B​ 1​ StartDate​ 1-Jan-16​ 2​ EndDate​ 29-Feb-16​ 3​ DayName​ Mon​

This formula returns the count of DayName within the date range, excluding holidays
Replace Holiday_List with a reference to your list of holidays
Code:
``C1: =NETWORKDAYS.INTL(B1,B2,REPLACE("1111111",MATCH(B3,{"Mon","Tue","Wed","Thu","Fri","Sat","Sun"},0),1,"0"),Holiday_List)``

In that example, the formula returns: 9
There are 9 Mondays from 01/01/2016 through 02/29/2016

Is that something you can work with?

Thank you, Mackers. Your second formula does exactly what I need.
Jonmo, I guess I should have included in this post that I'm using Excel 2007.

Thanks again, both of you.

Thank you, Ron. Will this work with Excel 2007? Mackers provided a good solution, but I'm always open to learning others.

No, the really neat date functions arrived in Excel beginning with Excel 2010.

Hmmm... Not sure when my employer plans to upgrade, but I can try. Thank you.

Code:
``=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&B3)),"ddd")="Mon"))``

Nice.

If you wanted to make that account for holidays, try

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(B2&":"&B3)),"ddd")="Mon"),--(ISNA(MATCH(ROW(INDIRECT(B2&":"&B3)),Holiday_List,0))))

Last edited:
Jonmo, after all of these edits, my holiday list is now in R5:R10. How does your formula look there?

Thank you.

Replies
7
Views
163
Replies
13
Views
373
Replies
8
Views
328
Replies
3
Views
381
Replies
1
Views
222

1,219,580
Messages
6,149,115
Members
450,861
Latest member
metcala

### 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.

### Which adblocker are you using?

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

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