Calculating # of working days between dates

[Max]

This is in addition to my last question. From www.mvps.org
I found the following function. I am just clueless on how
to apply it to my control source....help??!

I have an expression calculating the number of days
between two dates. This also takes in account if one date
is null. What I forgot to put in the mix was to calculate
the days excluding Saturday and Sunday.
Here is the control source, and the recommended function.
Not sure how to merge.

=IIf(IsNull([From A/E]),Date()-[To A/E],[From A/E]-[To
A/E])


'*********** Code Start **************
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
'*********** Code End **************

 

[Fons Ponsioen]

I copied the following from the Access help:
NETWORKDAYS
Returns the number of whole working days between a start
and end date, excluding weekends and any identified
holidays.
If this function returns the #NAME? error value, you may
need to install msowcf.dll.
Syntax
NETWORKDAYS(start_date,end_date,holidays)
Start_date is a date that represents the start date.
End_date is a date that represents the end date.
Holidays is an optional range of one or more days
(holidays) to exclude from the working calendar.

Hope this helps.
Happy New Year
Fons

 

[Douglas J. Steele]

While NETWORKDAYS may appear in the Help file, it's actually not available
in Access: it's an Excel function (the VBA help file has some problems...)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I copied the following from the Access help:
NETWORKDAYS
Returns the number of whole working days between a start
and end date, excluding weekends and any identified
holidays.
If this function returns the #NAME? error value, you may
need to install msowcf.dll.
Syntax
NETWORKDAYS(start_date,end_date,holidays)
Start_date is a date that represents the start date.
End_date is a date that represents the end date.
Holidays is an optional range of one or more days
(holidays) to exclude from the working calendar.

Hope this helps.
Happy New Year
Fons

-----Original Message-----
This is in addition to my last question. From www.mvps.org
I found the following function. I am just clueless on how
to apply it to my control source....help??!

I have an expression calculating the number of days
between two dates. This also takes in account if one date
is null. What I forgot to put in the mix was to calculate
the days excluding Saturday and Sunday.
Here is the control source, and the recommended function.
Not sure how to merge.

=IIf(IsNull([From A/E]),Date()-[To A/E],[From A/E]-[To
A/E])


'*********** Code Start **************
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
'*********** Code End **************

.