Calculating Times (Days)

[gselliott]

I have currently got a helpdesk system setup that generates Tasks via Microsoft Outlook, the operator selects a fix within time and then the number of days allocated to the call is worked out in code. The problem we are having is that if a call comes in on Thurdsay and the operator selects a Normal importance call then the call will be due to be complete on Sunday, this causes a problem as there is no staff here on a Sunday and jobs are being flagged up as overdue. Is there any way that I can calculate the days but miss out the weekend. For example

Call comes in on Tuesday but has a completion date of 5 working days instead of the job finishing on Sunday we would like it to finish on the following Tuesday which would be 5 working days.

Anyone got any ideas?

Thanks

 

[dan-cat]

Here is something I posted a while back:

Public Function GetDate() As Date

Select Case Weekday(DateAdd("w", -5, Date))
Case Is = 1
GetDate = Date - 7
Case Is = 7
GetDate = Date - 6
Case Else
GetDate = Date - 5
End Select
End Function


It grabs hold of the current date - subtracts 5 days from that and converts that value to the day number of the week.

If the returned value is 1 (Sunday) then seven days are taken away from the current date
If the returned value is 7 (Saturday) then six days are taken away from the current date.
Else only 5

 

[gselliott]

Thanks for that I will give it a try!

 

[Mile-O]

This will exclude all weekends over any number of days.

Function GetDate(intDays As Integer) As Date
    On Error GoTo Err_GetDate
    Dim dteTemp As Date
    dteTemp = Date
    Do While Not intDays = 0
        dteTemp = DateAdd("d", 1, dteTemp)
        If ((Not Weekday(dteTemp) = 1) And (Not Weekday(dteTemp) = 7)) Then _
            intDays = intDays - 1
    Loop
    GetDate = dteTemp
Exit_GetDate:
    Exit Function
Err_GetDate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_GetDate
End Function

 

[dan-cat]

too slow

 

[Mile-O]

Public Function GetDate() As Date

Select Case Weekday(DateAdd("w", -5, Date))
Case Is = 1
GetDate = Date - 7
Case Is = 7
GetDate = Date - 6
Case Else
GetDate = Date - 5
End Select
End Function

Too limited..

 

[dan-cat]

This will exclude all weekends over any number of days.

Function GetDate(intDays As Integer) As Date
    On Error GoTo Err_GetDate
    Dim dteTemp As Date
    dteTemp = Date
    Do While Not intDays = 0
        dteTemp = DateAdd("d", 1, dteTemp)
        If ((Not Weekday(dteTemp) = 1) And (Not Weekday(dteTemp) = 7)) Then _
            intDays = intDays - 1
    Loop
    GetDate = dteTemp
Exit_GetDate:
    Exit Function
Err_GetDate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_GetDate
End Function

too ornate

 

[Mile-O]

Okay, I cut it down by two lines and it now works over any number of days from any date.

Function GetDate(dteTemp As Date, intDays As Integer) As Date
    On Error GoTo Err_GetDate
    Do While Not intDays = 0
        dteTemp = DateAdd("d", 1, dteTemp)
        If ((Not Weekday(dteTemp) = 1) And (Not Weekday(dteTemp) = 7)) Then _
            intDays = intDays - 1
    Loop
    GetDate = dteTemp
Exit_GetDate:
    Exit Function
Err_GetDate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_GetDate
End Function

 

[gselliott]

Thanks for the replies I haven't had chance to try them out yet as I am unsure how I will tie this in with the code that I am using, this is what I have got so far:

Private Sub FixWithin_Click()
If FixWithin = "Critical" Then
FixByDate = Now
Time = "1 Day / 8Hrs"
End If

If FixWithin = "Urgent" Then
FixByDate = Now + 2
Time = "2 Days / 16Hrs"
End If

If FixWithin = "Normal" Then
FixByDate = Now + 3
Time = "3 Days / 24Hrs"
End If

If FixWithin = "Low" Then
FixByDate = Now + 5
Time = "5 Days / 40Hrs"
End If

Can anyone point me in the right direction?

Thanks.

 

[Mile-O]

With mine above:

Private Sub FixWithin_Click()
    Select Case Me.FixWithin
        Case Is = "Critical"
            Me.FixByDate = Now
            Me.Time = "1 Day / 8Hrs"
        Case Is = "Urgent"
            Me.FixByDate = GetDate(Date, 2)
            Me.Time = "2 Days / 16Hrs"
        Case Is = "Normal"
            Me.FixByDate = GetDate(Date, 3)
            Me.Time = "3 Days / 24Hrs"
        Case Is = "Low"
            Me.FixByDate = GetDate(Date, 5)
            Me.Time = "5 Days / 40Hrs"
    End Select

Although I must say that Time is probably one of the worst names for anything within a database as it is a reserved word. Consider using a prefix on your objects such as txtTime for a textbox and cboFixWithin for a combobox.

 

[gselliott]

Thanks I will give that a shot and I will change the Time name to txtTime.