andy_dyer
Registered User.
- Local time
- Today, 10:09
- Joined
- Jul 2, 2003
- Messages
- 806
Hi everyone - I've got a couple of interesting challenges that i don't know where to begin...
I have a WorkingDay module (found elsewhere on this forum ) that is working well for me elsewhere that feeds off a holiday table etc
(1) Calculated Target1 Date
With this challenge I have a start date field and a priority field - I need to be able to, based on the priority, set the target date in a calculated date field.
Priority 1 = 1 working day
Priority 2 = 3 working days
Priority 3 = 5 working days
i.e. if the start date was 25/01/2013 and it was a priority 1 then I want it to take the startdate field, calculate the target date based on 1 working day = 28/01/2013
How can I calculate this date in the future based on these requirements?
(2) Calculated Target2 Date
With this challenge I need to be able to take the previously calculated Target1 Date and calculate the next target date.
For this it can only be a Tuesday or a Thursday, so I need to be able to take Target1 Date and then calculate the date of the next Tuesday or Thursday.
i.e. if Target1 Date was Friday 25/01/2013 I want Target2 Date to calculate the next Tuesday or Thursday will be Tuesday 29/01/2013 etc
I've posted below my current module that helps me work out my working days in case any of this needs to be modified or added too...
Thanks to anyone who can help with these - I am really stuck...
I have a WorkingDay module (found elsewhere on this forum ) that is working well for me elsewhere that feeds off a holiday table etc
(1) Calculated Target1 Date
With this challenge I have a start date field and a priority field - I need to be able to, based on the priority, set the target date in a calculated date field.
Priority 1 = 1 working day
Priority 2 = 3 working days
Priority 3 = 5 working days
i.e. if the start date was 25/01/2013 and it was a priority 1 then I want it to take the startdate field, calculate the target date based on 1 working day = 28/01/2013
How can I calculate this date in the future based on these requirements?
(2) Calculated Target2 Date
With this challenge I need to be able to take the previously calculated Target1 Date and calculate the next target date.
For this it can only be a Tuesday or a Thursday, so I need to be able to take Target1 Date and then calculate the date of the next Tuesday or Thursday.
i.e. if Target1 Date was Friday 25/01/2013 I want Target2 Date to calculate the next Tuesday or Thursday will be Tuesday 29/01/2013 etc
I've posted below my current module that helps me work out my working days in case any of this needs to be modified or added too...
Code:
Option Compare Database
Option Explicit
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 Function
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
Thanks to anyone who can help with these - I am really stuck...