Calculating # of working days between dates

  • Thread starter Thread starter Max
  • Start date Start date
M

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 **************
 
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
 
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)



Fons Ponsioen said:
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 **************

.
Click to expand...
Click to expand...
 
Back
Top