Determin ing the number of business day in a given month

  • Thread starter Thread starter Michel Khennafi
  • Start date Start date
M

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
 
Hi
use NETWORKDAYS for this
Note: you have to install the Analysis Toolpak Addin for this
 
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
 
What could help determining the first day and the last day of the month in
that situation?

Since networkdays requires it...


Thanks!
 
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
Click to expand...

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
 
Back
Top