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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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?
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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?
 

OverKnight

New Member
Joined
Jun 13, 2011
Messages
10

ADVERTISEMENT

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.
 

OverKnight

New Member
Joined
Jun 13, 2011
Messages
10
Thank you, Ron. Will this work with Excel 2007? Mackers provided a good solution, but I'm always open to learning others.
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316

ADVERTISEMENT

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

OverKnight

New Member
Joined
Jun 13, 2011
Messages
10
Jonmo, after all of these edits, my holiday list is now in R5:R10. How does your formula look there?

Thank you.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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