kickstand07
New member
- Local time
- Today, 09:26
- Joined
- May 24, 2012
- Messages
- 6
Hey guys,
This is my first post on access-programmers, so be gentle please!
My issue is this:
I am trying to calculate the number of minutes between the opening of a ticket and its close using Access 2010. Obviously this is simple with DateDiff, but i also need to exclude holidays, weekends and non-business hours(Officially our hours are 8am-5pm)
I have very little experience with VBA or Access for that matter, so i have been basically moving forward with google as my only aid. So far i have come up with the code to exclude holidays(referencing a holiday table in my database) and weekends, but the function is returning a whole day number which i cannot simply multiply x 24 x 60 because i need an accurate count down to the minute.
For example:
Given open_date 5/18/2012 12:02:36 AM close_date 5/24/2012 1:50:43 AM the workdays function would return 5 (it successfully removed the two weekend days)
Here is the VBA code for the Weekdays and Workdays function:
First of all i would like to make this code accurate to the minute. After that i believe i can move forward with my efforts to remove times outside of 8am-5pm
If at any point I have been unclear, I would be happy to elaborate.
Thanks in advance for any help, and i apologize for my ignorance :banghead:
This is my first post on access-programmers, so be gentle please!
My issue is this:
I am trying to calculate the number of minutes between the opening of a ticket and its close using Access 2010. Obviously this is simple with DateDiff, but i also need to exclude holidays, weekends and non-business hours(Officially our hours are 8am-5pm)
I have very little experience with VBA or Access for that matter, so i have been basically moving forward with google as my only aid. So far i have come up with the code to exclude holidays(referencing a holiday table in my database) and weekends, but the function is returning a whole day number which i cannot simply multiply x 24 x 60 because i need an accurate count down to the minute.
For example:
Given open_date 5/18/2012 12:02:36 AM close_date 5/24/2012 1:50:43 AM the workdays function would return 5 (it successfully removed the two weekend days)
Here is the VBA code for the Weekdays and Workdays function:
PHP:
Option Compare Database
Option Explicit
Public Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday 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 ncNumberOfWeekendDays 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 endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If
' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1
' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = 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
Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String
' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)
nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If
strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"
' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)
Workdays = nWeekdays - nHolidays
Workdays_Exit:
Exit Function
Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit
End FunctionFirst of all i would like to make this code accurate to the minute. After that i believe i can move forward with my efforts to remove times outside of 8am-5pm
If at any point I have been unclear, I would be happy to elaborate.
Thanks in advance for any help, and i apologize for my ignorance :banghead:
