Count no of work Days in a month

**harishs** · 10-10-2007, 01:51 AM

Hi All,

How do i count no of days in a month; excluding saturday & sunday.

regards,
Harish S

**oldchippy** · 10-10-2007, 03:40 AM

Good morning Harish S,

Take a look at this link on NETWORKDAYS, you will need to add-in the Analysis Tool Pak

http://www.exceltip.com/st/Calculati..._days/844.html

http://office.microsoft.com/en-gb/ex...277241033.aspx

**dominicb** · 10-10-2007, 03:44 AM

Good morning harishs

Alternately, use this formula to return the number of days in the current month without the Analysis ToolPak add-in :
=DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

To return the number of days in a given month, replace the two "Now()" functions with a cell containing a valid date.

HTH

DominicB

**oldchippy** · 10-10-2007, 04:04 AM

Hi dominicb,

His question was how many weekdays - excluding weekends

How about

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

**VBA Noob** · 10-10-2007, 04:11 AM

Maybe a function

Function WorkDaysInMonth(Month As Integer, Yr As Integer) 
    Dim Holidays As Variant 
    Holidays = Array("1/1/" & Yr, "25/12/" & Yr, "26/12/" & Yr) 


    WorkDaysInMonth = networkdays( _ 
        DateSerial(Year(Now), Month, 0), _ 
        DateSerial(Year(Now), Month + 1, 0), Holidays) 
End Function

Put your holidays within the Array( ) statement. You'll need to
load the Analysis Tool Pack VBA add-in, and then in VBA go to the
Tools menu, choose References, and choose atvbaen.xls.

More information about working with holidays can be found at
www.cpearson.com/excel/holidays.htm

VBA Noob

**daddylonglegs** · 10-10-2007, 04:40 AM

If you're going to use ATP functions then you might as well use EOMONTH as well as NETWORKDAYS so for the number of workdays in the current month

=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0))

or for any month if you have the first day of the month in A1

=NETWORKDAYS(A1,EOMONTH(A1,0))

...or without using Analysis ToolPak functions....

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})+DAY(A1+30-DAY(A1+31)))/7))

**daddylonglegs** · 10-10-2007, 04:47 AM

Originally Posted by dominicb

Alternately, use this formula to return the number of days in the current month without the Analysis ToolPak add-in :
=DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

As oldchippy says, possibly not what the OP wants, but here's a way to get total days in the current month

=42-DAY(NOW()-DAY(NOW())+42)

**dominicb** · 10-10-2007, 06:39 AM

Good morning daddylonglegs

Just knew you'd jump into this thread somewhere

Originally Posted by daddylonglegs

=42-DAY(NOW()-DAY(NOW())+42)

How the hell does that work!?!?!?!

DominicB

**oldchippy** · 10-10-2007, 07:03 AM

=42-DAY(NOW()-DAY(NOW())+42)

It is a good one this, it seems like any number between 32 and 59 calculates correctly - or am I wrong?

**daddylonglegs** · 10-10-2007, 08:03 AM

Originally Posted by dominicb

Just knew you'd jump into this thread somewhere

I can never resist the lure of a juicy date [question]

Further to the above. Using similar logic, if you have the 1st of a month in A1 this formula gives the number of days in the month

=50-DAY(A1+49)

Count no of work Days in a month

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count no of work Days in a month

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions