Results 1 to 4 of 4
Thread: NetworkDays (XP)

20050317, 10:27 #1
 Join Date
 Oct 2002
 Location
 Leamington Spa, Warwickshire, England
 Posts
 136
 Thanks
 0
 Thanked 0 Times in 0 Posts
NetworkDays (XP)
can you add 30 working days to a date?
Michelle

20050317, 11:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: NetworkDays (XP)
Select Tools  AddIns...
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.

20050318, 03:14 #3
 Join Date
 Mar 2005
 Location
 Wellington, Wellington
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: NetworkDays (XP)
Slightly offtopic, 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 "MonFri", "MonSat" or "MonSun"  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.

20050318, 08:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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
 <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.
You don't use the variable DoNothing anywhere.
The RetVal argument you mention should be RetType.