1. ## NetworkDays (XP)

can you add 30 working days to a date?

Michelle

2. ## Re: NetworkDays (XP)

Make sure that Analysis ToolPak is ticked, then click OK.

The Analysis ToolPak contains functions for calculations involving working days.

Say you have a date in A1. Enter the following formula in B1:

=WORKDAY(A1,30)

This will add 30 working days to the date in A1. You will probably have to format B1 as a date.

3. ## Re: NetworkDays (XP)

Slightly off-topic, but the base logic can be used. I have had need to calculate the number of work days between two dates when the work pattern could be anything from one half day per week through to 14 half days, or working only Monday, Tuesday, Wednesday morning and Saturday night.

Function numWeeks(StartDate, EndDate, WkPat, Optional RetType)
WorkWeekDays = 0
Total = 0
If IsMissing(RetType) Then RetType = 1

If StartDate > EndDate Then
temp = EndDate
EndDate = StartDate
StartDate = temp
End If

Select Case WkPat
Case 5
WkPat = 1111100
Case 6
WkPat = 1111110
Case 7
WkPat = 1111111
End Select

WkPat = Format(WkPat, "0000000")
For wkDay = 1 To 7
WorkDay = Mid(WkPat, wkDay, 1)
If WorkDay = "0" Then
DoNothing = True
Else
WorkWeekDays = WorkWeekDays + 1 / Val(WorkDay)
End If
Next wkDay

NumDays = EndDate - StartDate + 1
myWeeks = Int(NumDays / 7)
pStartDate = StartDate + myWeeks * 7

If pStartDate <= EndDate Then
For CheckDate = pStartDate To EndDate
myWeekday = WeekDay(CheckDate, vbMonday)
WorkDay = Mid(WkPat, myWeekday, 1)
If WorkDay = "0" Then
DoNothing = True
Else
Total = Total + 1 / Val(WorkDay)
End If
Next CheckDate
End If

TotalWeeks = myWeeks + Total / WorkWeekDays
Whole = Int(TotalWeeks)
Part = TotalWeeks - Whole
Part = Format(Part * WorkWeekDays, "0")

Select Case RetType
Case 1
numWeeks = TotalWeeks
Case 2
numWeeks = Whole & "w " & Part & "d"
Case 3
numWeeks = Whole & Plurals(" week", Whole) & ", " & Part & Plurals(" day", Part)
Case Else
numWeeks = "Err"
End Select
End Function

The "plurals" function referred to simply determines correct grammar for the response. I needed different responses for different purposes, hence the RetVal parameter.

The Work Pattern can be used either with "5", "6", or "7" being "Mon-Fri", "Mon-Sat" or "Mon-Sun" - added for convenience - or a 7 character/digit value - eg. 1112200 where each 1 represents a whole day, 2 represents a half day, and 0 represents not worked at all.

I think it is still quite crude and can be made more elegant, but it might be useful to someone.

Gravey.

4. ## Re: NetworkDays (XP)

That's an interesting function, but I have a few nits to pick:

You haven't declared the local variables in your function. You should
1. <LI>Tick "Require variable declaration" in Tools | Options... (in the Visual Basic Editor).
<LI>Insert a line Option Explicit at the top of all existing code modules.
<LI>Declare all variables you use explicitly.
Instead of using IsMissing, you can use Optional RetType As Long = 1 in the function header.
You don't use the variable DoNothing anywhere.
The RetVal argument you mention should be RetType.

#### Posting Permissions

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