M
Max
Hello.
I am trying to calculate the working days between two
dates in an IIF formula. Problem is that the module I am
using (Below) seems to return calculate # of weeks and not
# of days. Any ideas.
Max
Option Compare Database
Option Explicit
'*********** 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 am trying to calculate the working days between two
dates in an IIF formula. Problem is that the module I am
using (Below) seems to return calculate # of weeks and not
# of days. Any ideas.
Max
Option Compare Database
Option Explicit
'*********** 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 **************