calculating working days

[hights]

I am trying to work out standards for 3 different priorites eg...

Product was booked in today 03/10/14..

Proirty 1 - needs to be worked in 3 working days
Proirty 2 - needs to be worked in 10 working days
Proirty 3 - needs to be worked in 30 working days

I would like the query to display the standard date . eg 03/10/14 priorty 1 would dispay 07/10/14 ( doesnt include weekends)

the booking in date is displayed in a table, just need a query to work out the above. I have been trying to find a query that will work this out but no luck :banghead:

 

[plog]

I think every month for the last 3 months there's been at least one post on this forum about calculating work days from a given date. Search this forum to find those posts.

In general here's how you solve this--make a function in a module to calculate the new date by passing it the date to start and the number of working days to add to it. The function determines that date and passes you back the correct value.

Again, this is a common issue and I've seen a few posts recently that you can grab the code for this issue for.

 

[BlueIshDan]

Example of function used in form.

Hope this helps. I'm sure there could be bugs in certain situations but this quickly built function should set you well on your way to a solution.

Option Compare Database
Option Explicit

Private Sub btnSubmit_Click()
    lblResultEndDate.Caption = GetWorkDateDiff(dtpDate.value, txtWorkDays.value, CBool(chkCurrentDay.value))
End Sub

Private Function GetWorkDateDiff(start_date, days, count_first_day As Boolean) As Date
    If isValidValue(start_date, "Date") Then
        If isValidValue(days, "Number") Then
            Dim work_days As Integer: work_days = CInt(days)
            Dim due_date As Date: due_date = DateAdd("d", IIf(count_first_day, 0, -1), start_date)
            
            While work_days > 0
                due_date = DateAdd("d", 1, due_date)
                If Not (Weekday(due_date) = 1 Or Weekday(due_date) = 7) Then: _
                    work_days = work_days - 1
            Wend
            
            GetWorkDateDiff = due_date
        Else
            MsgBox "Invalid Value, must be non-null and a whole number"
        End If
    Else
        MsgBox "Invalid Value, must be non-null and date value."
    End If
End Function


Private Function isValidValue(value, type_name) As Boolean
    
    isValidValue = (Len(value & vbNullString) > 0 And _
                    IIf( _
                        TypeName(value) = "string" And type_name = "number", _
                            IsNumeric(value), _
                        type_name = TypeName(value) _
                        ) _
                    )
End Function

 

[hights]

Thanks BlueIshDan, works perfectly. exactly what I needed !