Determin ing the number of business day in a given month

[Michel Khennafi]

Good day to all of you!

Could anyone provide some help in the following area:
- I would like to calculate the number of business days within a given month
for a given year.

I have the month in A1 (format "Dec-04")
I would like to put the number of business days in A2 based on the value of
A1 (all days except Saturday / sundays)

Has anyone done such calculation ? If yes, thanks for sharing the formula!

Happy holidays to all of you

 

[Frank Kabel]

Hi
use NETWORKDAYS for this
Note: you have to install the Analysis Toolpak Addin for this

 

[Guest]

One way

=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1)+1,0))

you can also put in public holidays as a third argument, you need to have
ATP installed (comes with Excel)

This assumes the Dec-04 is a real date and not text, then you would need to
parse it to a date

Regards,

Peo Sjoblom

 

[Michel Khennafi]

What could help determining the first day and the last day of the month in
that situation?

Since networkdays requires it...


Thanks!

 

[Frank Kabel]

Hi
Peo's formula already does this. A1 is any date in your desired month

 

[Ron Rosenfeld]

Good day to all of you!

Could anyone provide some help in the following area:
- I would like to calculate the number of business days within a given month
for a given year.

I have the month in A1 (format "Dec-04")
I would like to put the number of business days in A2 based on the value of
A1 (all days except Saturday / sundays)

Has anyone done such calculation ? If yes, thanks for sharing the formula!

Happy holidays to all of you

Peo's formula is good, and requires that the Analysis Tool Pak be installed.

If you do not want to install this, the following formula (with some date in
A1) will also give you the number of non-weekend days in any month:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+
1&":"&DATE(YEAR(A1),MONTH(A1)+1,0))),3)<5))


--ron