# Thread: No. of Working Days in month?

1. ## No. of Working Days in month?

Hello,

Is there a formula to calculate the No. of Working Days in a month (as per the no. of days in that month).

I'd like to fill out the attached table

2. ## Re: No. of Working Days in month?

If cell B5 contains the date of the first day of the month then you can use
<font face="Georgia">=NETWORKDAYS(B5,EOMONTH(B5,0))</font face=georgia>

StuartR

(I have moved this thread from Scuttlebut to Excel to make it more visible to others who may be able to give you answers)

3. ## Re: No. of Working Days in month?

This will depend on (easy) the number of weekdays in that particular month in that particular year BUT (more difficult) also on what public holidays your country/state/town has. The days on which they can occur vary -- sometimes fixed to particular days in paryticular months (Christmas Day & Boxing Day, at least), some vary (Good Friday & Easter Monday, at least). A couple of years' ago we had a "special" Bank Holiday for the Queen's 50th Anniversary of being Queenie, which was exceptional.

Calendar calculations are a complete pain, and I find the easiest way is to do it is "by inspection" of a calendar which is marked up with all the public holidays. One instance where the brain is mightier than the PC (even if it were an Apple Mac!)...

John

4. ## Re: No. of Working Days in month?

Dear Stuart,

I tries your formula but it seemed to produce an error.
I've now attached the Excel File.
Can you please apply the formula to it & then repost the updated Excel file?

Thanks,

BD.

5. ## Re: No. of Working Days in month?

If you look at the Excel help for EOMONTH you will see that it says
<hr>If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.<hr>

If you look at the help for NETWORKDAYS you will see the same information about the Analysis ToolPak as well as the syntax for including an array of holiday dates as a third parameter

StuartR

6. ## Re: No. of Working Days in month?

Hi,

The attached screen shot shows the formula I've entered & the error.
Any clues would be appreciated.

Cheers,

BD.

7. ## Re: No. of Working Days in month?

Tick the Analysis Toolpak
Click OK.
Functions such as EOMONTH should now be available.

8. ## Re: No. of Working Days in month?

Hi,

I've added the Analysis Tool Pak & all the rest!
Still I'm getting the error as shown.

If you can download the Excel File I attached earlier in this thread & try this for yourself, you should get the same error.

Maybe there's a small typo we can fix to get over this problem.
I copy+pasted the text from the message exactly as it was.

Help!

TIA,

BD.

9. ## Re: No. of Working Days in month?

Also Stuart assumed that in his formula that "B5" (the "A2" in your adaptation) is the date of the first day of the month, not the number of days in the month.

To get the number of days in a month (total or networkingdays) you need the month number and the year. You might also want to include a list of holidays.

If A2:A13 has the dates of the frist day of each month, the total days in a month are given by:
<pre>=DAY(EOMONTH(A3,0))</pre>

which can be copied down the rows

Net working days (ignoring holidays) is
<pre>=NETWORKDAYS(A3,EOMONTH(A3,0))</pre>

If the range A15:A30 contains a listing of dates that are holidays, then the Net working days (excluding the holidays) is
<pre>=NETWORKDAYS(A3,EOMONTH(A3,0),\$A\$15:\$A\$30)</pre>

Steve

10. ## Re: No. of Working Days in month?

Column A should contain the first day of a month, as indicated by Stuart.

11. ## Re: No. of Working Days in month?

Hans,
Did you do something "odd" to the this file or do the "addin" functions not get converted?
Usually when I open up "non-US" excel files, I see the US functions in the cells. In this file you attached, they did not get converted. They both are Analysis pack "add-ins" so my "speculation" is that these do not get "translated", which I had not heard about. (but since I deal with the US versions, I generally don't see any of these "foreign conversion" problems)

For Bob's benefit I am also attaching a file which expands upon Hans' file and include the formulas that I discussed. Note: your company Holiday's will vary, and not all of these are real "holidays"

Steve

12. ## Re: No. of Working Days in month?

Hi Steve,

I didn't do anything special, I just used the Analysis Toolpak functions in the Dutch version of Excel. If they still show as NETTO.WERKDAGEN en LAATSTE.DAG for you, that implies that Excel stores these add-in functions as text, unlike the native Excel functions - those are stored in a language-independent way.

13. ## Re: No. of Working Days in month?

I guess this is just another thing for people working across "multi-language" applications that they need to be aware of...

Steve

14. ## Re: No. of Working Days in month?

Yes indeed. I just checked what happens if I record entering some functions in a macro.
I entered all functions in Dutch, but the recorded instruction contains the English version of built-in functions, such as MONTH, and the Dutch version of Analysis Toolpak functions, such as EOMONTH.

15. ## Re: No. of Working Days in month?

Hello,

Ok, now here's an interesting twist on the challenge.

Let's say one of our customers is in the Middle East; there the weekends are Thursday and Friday, not Saturday and Sunday.

So in a new version of the same Excel File, I want to incorporate that difference.
How can I do it?

The current version won't work for them because for example, Saturday 31st January is a vacation there (usually it's a Working Day). Here in England, it's a weekend.

So the calculation ignores this "vacation" as it's a Weekend anyway.

An analogy might be like in Microsoft Project where you can specify a customised Non-Working Time and thus adjust the working days as you like.

Can I solve this problem?

Thanks,

BD.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•