DateDiff - for workdays?

[ppataki]

Dear All,

I was trying to use datediff function to calculate the nr of working days between two dates, but it does not matter if I use "d" or "ww" or whatever parameter it just gives me the difference in days and not in working days

Could you please advise?

Many thanks in advance!

 

[Dennisk]

how would it know what working days are. what happens when there is a bank holiday in England but not Scotland. You will have to calculate this yourself.

 

[ppataki]

Let's say that it needs to exclude saturdays and sundays only

 

[namliam]

Have a search on the references part of the forum for this, a couple of samples there.

 

[Guus2005]

I found this on www.planetsourcecode.com
It is written in VB so it might need a little work.

HTH

 

[ppataki]

I think this is a bit easier, I have just found it

Function CalcWorkdays(StartDate, EndDate) As Integer
    Dim LTotalDays As Integer
    Dim LSaturdays As Integer
    Dim LSundays As Integer
    On Error GoTo Err_Execute
    CalcWorkdays = 0
    If IsDate(StartDate) And IsDate(EndDate) Then
        If EndDate <= StartDate Then
            CalcWorkdays = 0
        Else
            LTotalDays = DateDiff("d", StartDate - 1, EndDate)
            LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
            LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
            'Workdays is the elapsed days excluding Saturdays and Sundays
            CalcWorkdays = LTotalDays - LSaturdays - LSundays - 2
        End If
    End If
    Exit Function
Err_Execute:
    'If error occurs, return 0
    CalcWorkdays = 0
End Function

Then it can be called from a query

 

[iuphim]

Is this like creating a calendar then rewrite queries to do calculations? Further explanations please. Maybe an example would be great in how the final report would look like.

Thanks

I think this is a bit easier, I have just found it

Function CalcWorkdays(StartDate, EndDate) As Integer
    Dim LTotalDays As Integer
    Dim LSaturdays As Integer
    Dim LSundays As Integer
    On Error GoTo Err_Execute
    CalcWorkdays = 0
    If IsDate(StartDate) And IsDate(EndDate) Then
        If EndDate <= StartDate Then
            CalcWorkdays = 0
        Else
            LTotalDays = DateDiff("d", StartDate - 1, EndDate)
            LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
            LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
            'Workdays is the elapsed days excluding Saturdays and Sundays
            CalcWorkdays = LTotalDays - LSaturdays - LSundays - 2
        End If
    End If
    Exit Function
Err_Execute:
    'If error occurs, return 0
    CalcWorkdays = 0
End Function

Then it can be called from a query