Calculate the number of workdays in a given month

OverKnight

New Member
Joined
Jun 13, 2011
Messages
10
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Thank you, Ron. Will this work with Excel 2007? Mackers provided a good solution, but I'm always open to learning others.
 
Upvote 0
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:
Upvote 0
Jonmo, after all of these edits, my holiday list is now in R5:R10. How does your formula look there?

Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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