Count the no. of weekdays in a month

[SimLee]

Dear sir,
Would you mind teaching me how to count the number of
weekdays in a particular month?
A formula being able to calculate the no of weekdays in a
particular month in a particular year but Not needing to
input the "End Date" and the "start
date" of that month to get the answer?

Coz I need this formular to automatically calculate out
the number of weekdays in everymonth in this and coming
years.

Thanks so much.

Cheers,
Simon Lee

 

[Peo Sjoblom]

Where would you select the month in question if you are not using dates?
If it is the current month

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(TODAY()),1)&":"
&DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))),2)<6))

you would always need start and end date, by using today you don't need to
enter the date
If you want to get the dates for 2004, put 2004 in A1 and copy down this
formula 12 rows

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A$1,ROW(1:1),1)&":"&DATE($A$1,ROW(
1:1)+1,0))),2)<6))

 

[Norman Harker]

Hi SimLee!

Put a date in (say) A1 (make sure that it is a date before the 29th of
the month or you can hit problems).
In the cell below use:
Either:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Or:
=EDATE(A1,1)
Copy down
In B1 use:
=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,0))
Copy down

You are still inputting the start date and end date in the NETWORKDAYS
function but these are input using the DATE function to derive the
first and last days of the month.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Bob Phillips]

Simon,

By start date and end date, I assume you mean 01/ etc. You need some sort of
date to work from, so assuming a date in your target month in A1, this works

=DATE(YEAR(A1),MONTH(A1)+1,1)-DATE(YEAR(A1),MONTH(A1),1)

If you just want to input a month, using this year, try this

=DATE(YEAR(TODAY()),A1+1,1)-DATE(YEAR(TODAY()),A1,1)
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, ignore this, mis-read it completely.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Daniel.M]

Hi,

If you want to handle holidays.
With:
the holidays in a range
a date of a month in A1

=NETWORKDAYS(EOMONTH(A1,-1)+1,EOMONTH(A1,0),Holidays)

If you don't need to handle holidays, here's a formula that doesn't require the
Analysis Toolpak:

=SUM(INT((31-DAY(A1-DAY(A1)+32)+WEEKDAY(A1-DAY(A1)-{1;2;3;4;5}))/7))


If you're using year and month as an input (without holidays):
In B1: 2004 ' or whatever
In B2: 2 ' for Feb

=20+SUM(--(DAY(DATE(B1,B2,36)-WEEKDAY(DATE(B1,B2,-{1;2;3;4;5})))>7))

Regards,

Daniel M.