Calculating NetWorkDays

[Angela Peterson]

Excel has a built-in function that returns the number of
whole working days between a start_date and end_date that
will exclude weekends and any dates identified in
holidays.

Is there a way in access to create a calculated control on
a form or report that does the same thing. I'm thinking
that Iif statement might work since the function is not
present in the expression builder.

Thanks!

 

[StCyrM]

Hi Angela

Here's afunction that will calculate working days between 2 dates.

Hope this helps..

Best regards

Maurice St-Cyr
Micro Systems Consultants, Inc.

Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

 

[Angela Peterson]

Thank you! It worked great!

A.

-----Original Message-----
Hi Angela

Here's afunction that will calculate working days between 2 dates.

Hope this helps..

Best regards

Maurice St-Cyr
Micro Systems Consultants, Inc.

Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function





.