Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    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

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

    Re: NetworkDays (XP)

    Select Tools | Add-Ins...
    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. #3
    New Lounger
    Join Date
    Mar 2005
    Location
    Wellington, Wellington
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

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