Dear All,
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.
So, i select the module for Access 2010 but also not finish with function i hope i could able to finalize it with you guys help.
Below is coding for function...please help to complete and kindly correct the wrong coding.
Table name is "LeaveSettlement" and column name is "Total_Wdays" please also tell me what field type i have to select for result column.
_________________________________________________________
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error
' The number of weekend days per week.
Const LeaveSettlement As Integer = 2
' The number of days inclusive.
Dim varDays As Variant
' The number of weekend days.
Dim varWeekendDays As Variant
' Temporary storage for datetime.
Dim dtmX As Date
' If the end date is earlier, swap the dates.
If Leave_End < Leave_Start Then
dtmX = Leave_Start
Leave_Start = Leave_End
Leave_End = dtmX
End If
' Calculate the number of days inclusive (+ 1 is to add back Leave_Start).
varDays = DateDiff(Interval:="d", _
date1:=Leave_Start, _
date2:=Leave_End) + 1
' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=Leave_Start, _
date2:=Leave_End) _
* LeaveSettlement) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_Start) = vbFriday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_End) = vbSaturday, 1, 0)
' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)
Weekdays_Exit:
Exit Function
Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function
_________________________________________________________
Thanks & regards,
MBA
I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.
So, i select the module for Access 2010 but also not finish with function i hope i could able to finalize it with you guys help.
Below is coding for function...please help to complete and kindly correct the wrong coding.
Table name is "LeaveSettlement" and column name is "Total_Wdays" please also tell me what field type i have to select for result column.
_________________________________________________________
Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error
' The number of weekend days per week.
Const LeaveSettlement As Integer = 2
' The number of days inclusive.
Dim varDays As Variant
' The number of weekend days.
Dim varWeekendDays As Variant
' Temporary storage for datetime.
Dim dtmX As Date
' If the end date is earlier, swap the dates.
If Leave_End < Leave_Start Then
dtmX = Leave_Start
Leave_Start = Leave_End
Leave_End = dtmX
End If
' Calculate the number of days inclusive (+ 1 is to add back Leave_Start).
varDays = DateDiff(Interval:="d", _
date1:=Leave_Start, _
date2:=Leave_End) + 1
' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=Leave_Start, _
date2:=Leave_End) _
* LeaveSettlement) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_Start) = vbFriday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=Leave_End) = vbSaturday, 1, 0)
' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)
Weekdays_Exit:
Exit Function
Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function
_________________________________________________________
Thanks & regards,
MBA