Working Days

[Hazel]

Can anyone help
I have one field that you enter a start date
the next field is the start date that adds 28 days to give a due date
but this includes saturdays and sundays which i dont want i only want the working week/day.
Please help

 

[Ukraine82]

Hazel,

Take a look at the attachment file.

hth,
Michael

 

[Mile-O]

Put the following in a new module:

Public Function WorkingDays(ByVal lngDays As Long, ByVal dteStart As Date, _
    ByVal booDirection As Boolean) As Long
    
    Do While lngDays <> 0
        Select Case Weekday(dteStart)
            Case Is = 2, 3, 4, 5, 6
                lngDays = lngDays - 1
        End Select
        If booDirection Then
            dteStart = dteStart + 1
        Else
            dteStart = dteStart - 1
        End If
    Loop
    
    WorkingDays = dteStart
    
End Function

In your query, you can use the function above.

i.e.


FutureDate: WorkingDays(28, Date(), True)


The final argument determines the direction. True counts forward. False counts backwards.

 

[sonny]

Ive seen a few posts on this topic. Some had excluding holidays. The holiday ones required a table holding the holiday dates.

Is it possible to also have a table with forcasted leave dates like holidays and have it return a count of all work days between a start and end date that excludes holidays and any dates for a persons leave dates?

Say I want to assign someone work that starts 01/01/2006 and the work had to be done by 01/05/2007....

There are a number of holidays within those dates...

And the person I may want to assign has 2 forcasted leave requests for say 02/05/2006 thru 02/09/2006 Plus 06/09/2006 thru 06/19/2006 ...

Can this be done ???