Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Quedgeley, Gloucester, England
    Posts
    5,333
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    <font face="Script MT Bold"><font color=blue><big><big>John</big></big></font color=blue></font face=script>

    Ita, esto, quidcumque...

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No. of Working Days in month?

    Select Tools | Add-Ins...
    Tick the Analysis Toolpak
    Click OK.
    Functions such as EOMONTH should now be available.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No. of Working Days in month?

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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #15
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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