Count no of work Days in a month

[harishs]

Hi All,

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

regards,
Harish S

[oldchippy]

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]

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]

Hi dominicb,

His question was how many weekdays - excluding weekends

How about

Please Login or Register  to view this content.

[VBA Noob]

Maybe a function

Please Login or Register  to view this content.

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]

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]

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]

Good morning daddylonglegs

Just knew you'd jump into this thread somewhere

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

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

DominicB

[oldchippy]

Please Login or Register  to view this content.

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

[dominicb]

seems like any number between 32 and 59 calculates correctly - or am I wrong?

I get it working from 32 to 61.
But that doesn't help me figure out how it works...

DominicB

[oldchippy]

I still don't know how it works, but if you change the NOW() to 01/02/2007, your numbers throw up an incorrect answer - some months work below 32 or above 59, but all dates seem to work between 32 and 59.

We're waiting DADDY!

[daddylonglegs]

Yes, any number from 32 to 59 guarantees that it works.

=NOW()-DAY(NOW()) gives you some time on the last day of last month, If you then add any number between 32 and 59 you're guaranteed to be somewhere in next month.

Take 32 for instance, the formula

=DAY(NOW()-DAY(NOW())+32)

Will return 1 if current month has 31 days, 2 if current month has 30 days, 3 if current month has 29 days or 4 if current month has 28 days.

So deduct that number from 32 and you have the number of days in the current month

=32-DAY(NOW()-DAY(NOW())+32)

I like to suggest 42 because that number, of course, is the Answer to life, the universe and everything

[JustinBram]

Hitchhikers guide to the galaxy, quality DLL

[daddylonglegs]

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)

[dominicb]

Hi daddylonglegs

So deduct that number from 32 and you have the number of days in the current month

Aaaaaaahhhhhh! Clever.

I like to suggest 42 because that number, of course, is the Answer to life, the universe and everything

...but now you've just gone down in my estimation. Must try harder...

I can never resist the lure of a juicy date

And why would you. Knew it was right up your street. Just thought I'd answer it wrong for a laugh

DominicB

[oldchippy]

Please Login or Register  to view this content.

That's another good one too for the file - thanks daddylonglegs

[cjtwhidbey]

I am attempting to count the Days in a month excluding holidays and weekends.
This formula "=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0))" counts holidays as regular non week end days

[dominicb]

Good evening cjtwhidbey

...and welcome to the forum!!
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

DominicB