# Thread: Network Days (XP)

1. ## 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. ## 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>

3. ## 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. ## 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. ## Re: Network Days (XP)

Thanks Hans. I'll give it a try.

6. ## 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. ## 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. ## 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.)

9. ## 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. ## 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.

11. ## 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.

12. ## Re: Network Days (XP)

Thank you once again.

13. ## 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. ## 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. ## 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
•