Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Time Calculation

    Hi There,

    I also have another thread going to discuss this issue. However, I have found a better way to solve my problem of the number of workdays between two dates. I have the following code, which I am using to calculate how many days an employee has requested for vacation. I can then see how many are remaining, etc.

    My problem is that if the date is on the same day because the employee only wants half a day, the end result is zero days taken, but it should be .5. The best way around this, in my opinion, is to do the calculations using hours instead of days.

    Can you somebody help me to modify the code to pull hours instead of days. At the moment I have the dates and time separated into two fields to make it easy for the employee.

    Public Function Workdays(ByRef startDate As Date, _
    ByRef endDate As Date, _
    Optional ByRef strHolidays As String = "Holidays" _
    ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive. Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String

    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)

    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
    Workdays = -1
    GoTo Workdays_Exit
    End If

    strWhere = "[Holiday] >= #" & startDate _
    & "# AND [Holiday] <= #" & endDate & "#"

    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
    Domain:=strHolidays, _
    Criteria:=strWhere)

    Workdays = nWeekdays - nHolidays

    Workdays_Exit:
    Exit Function

    Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Workdays"
    Resume Workdays_Exit

    End Function

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would suggest a simple change to your If statement:

    If nWeekdays = -1 Then
    Workdays = -1
    elseif nweekdays = 0 then
    workdays = 0.5
    End If
    John M Reynolds

  3. #3
    Join Date
    Aug 2009
    Posts
    61
    Quote Originally Posted by jmrSudbury View Post
    I would suggest a simple change to your If statement:

    If nWeekdays = -1 Then
    Workdays = -1
    elseif nweekdays = 0 then
    workdays = 0.5
    End If
    This is still returning 1 for the day, even though they have booked only .5, or 4 hours. The calculation is not looking at any hours, only days. I have two fields, [VacRequestSrtTime] for the start time and [VacRequestFinTime] for the end time. How can I integrate these in so that it counts the hours as well instead of just the days?

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    How does the user input a half day?

    i.e. How does your function accept (13/08/2011, 13/08/2011) and know if the user is taking a full day or a half day?
    Looking for the perfect beer...

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    Quote Originally Posted by kez1304 View Post
    How does the user input a half day?

    i.e. How does your function accept (13/08/2011, 13/08/2011) and know if the user is taking a full day or a half day?
    I also have fields for start time and end time. They request that they want to leave at 11:30 am, which means that they require a half day vacation.

    i.e. 13/08/2011 7:30:00 until 13/8/2011 11:30:00 am

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You'd need to check those fields to see if they're <= 4 hours apart, if so, and the startDate and the endDate are equal, .5 should be returned.

    If they're > 4 hours apart, then a full day will be required.

    Personally, I'd incorporate that into the same function, but it's up to you really.
    Looking for the perfect beer...

Posting Permissions

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