Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Network Days (XP)

    Is there a method to calculate network days without having a start date or end date? For example entering something like =networkdays(april, 2007). If it can't be done this way how about with VBA.

    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    I'm not sure what you are looking for. If you want the number of workdays in April 2007, then you can use:

    <code>
    =NETWORKDAYS(DATE(2007,4,1),DATE(2007,4,30))
    </code>
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    I don't want to have to put the first day of April or the last day of April into some formula. I would like to be able to have Excel calculate the number of network days upon entering the month and year. As stated in my original question, something like =networkdays (April 2007).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Network Days (XP)

    Here is a user-defined function you can use:
    <code>
    Function WorkdaysInMonth(TheMonth)
    Dim dtm29 As Date
    Dim dtmLast As Date
    Dim dtmDate As Date
    dtm29 = DateSerial(Year(TheMonth), Month(TheMonth), 29)
    dtmLast = DateSerial(Year(TheMonth), Month(TheMonth) + 1, 0)
    WorkdaysInMonth = 20
    For dtmDate = dtm29 To dtmLast
    WorkdaysInMonth = WorkdaysInMonth - (Weekday(dtmDate, vbMonday) < 6)
    Next dtmDate
    End Function
    </code>
    You can use it in several ways:

    1) Supply the month as a string (between quotes)
    <code>
    =WorkdaysInMonth("April 2007")
    </code>
    2) Enter the month (without quotes) in a cell, for example in A1, and enter the following formula in another cell:
    <code>
    =WorkdaysInMonth(A1)</code>

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    Thanks Hans. I'll give it a try.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    OK, I am not sure how I should use a UDF. I copied the inforamtion and put it into the code. When I tried your examples I get #name in the cell. I assume I have to define a name, but not sure how to do so with a UDF.

    Also, would you mind explain exactly what this does. I see that you stated in the code the number of workdays in a month =20. However many months have more than 20 work days.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Network Days (XP)

    1) See <!post=User-defined functions,380241>User-defined functions<!/post>

    2) All months have at least four full weeks = 20 workdays (holidays aren't taken into account). There is a short loop that checks how many of the remaining days (which can vary from 0 for February in non-leap years to 3 for January, March etc.) are workdays.; this is added to the start value of 20.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Network Days (XP)

    It would be easier to enter your required month into a cell, say A1, and then have this formula:
    <code>=NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE (YEAR(A1),MONTH(A1)+1,0))</code>
    If you enter April 07 in a cell, Excel will convert it to 1 April 2007 and the formula then calculates the last day for you (It is also set up so that if you entered 5 April 07 it will still calculate the whole of April.)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    <P ID="edit" class=small>(Edited by kiazd on 12-Mar-07 16:18. Noticed more months in 08 are not calculating properly.)</P>Thanks for the formula. However when I ran it from April 07 to March 08 I noticed two discrepancies. According to my calendar, Feb 08 will have 21 networkdays. This formula accounts for only 20. Again my calendar shows March as having 21 networkdays, while the formula results in 22. I assume this is the result of 08 being a Leap Year and the formula does not know how to deal with the extra day in Feb properly.

    Upon further checking in 08, I find subsequent months are also not calculating properly.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    That is because, if you enter Feb 08, Excel interpretes that as Feb 8, 2007. If you enter Feb 2008 you will get the answer you want. Same for all dates with a month name and a two digit number. If the number is less than the number of days in the month name, Excel will interpret it as a day number and assume the current year.
    Legare Coleman

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Network Days (XP)

    Apologies, Legare is correct if you have US date settings (and possibly others). On UK date settings, if I enter Feb 08, I get 1 Feb 2008. Either way, if you enter a 4 digit year, you should be fine.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    Thank you once again.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    Rory, I understand how you can exclude holidays as shown in the Excel help files. I am wondering whether or not your formula could be modified to exclude holidays.
    Thanks.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Network Days (XP)

    NETWORKDAYS has an extra argument to specify the list of holidays. This can be a cell reference or a named range (or an array of values). Say your holidays are in Sheet2!H1:H100.

    =NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR( A1),MONTH(A1)+1,0),Sheet2!H1:H100)

    or if you have created a named range Holidays:

    =NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR( A1),MONTH(A1)+1,0),Holidays)

  15. #15
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Network Days (XP)

    Duh! <img src=/S/bash.gif border=0 alt=bash width=35 height=39> So simple. Thanks.

Posting Permissions

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