David Bigelow
asked on
How could this be modified to find end dates after weeks or months?
Previously, I asked about finding a date after x business days. But, to Gustav's point, "for longer intervals you typically count in weeks or months," I believe that for the intervals of weeks and months, it would be better to use those units of measure.
How could I modify the solution here to include weeks and months?
https://www.experts-exchange.com/questions/28741399/How-do-you-determine-a-date-after-x-number-of-business-days.html
The interval is coming from a drop down in the cboPriority.Column(2) field.
Me!FollowupDate.Value = DateAddWorkdays(Val(Me!cbo Priority.C olumn(2)), Me!OpenDate.Value)
currently they are all listed as business days
1
2
5 (1 week)
15 (3 weeks)
44 (1 month)
I really don't know what would be a good way to refer to weeks or months while indicating a change in unit of measure from days.
Is it best just to leave it as days? I was just looking for a more precise way that a person could look at the difference between the opening date and the follow up date and say, yes, that is 3 weeks or 2 months between, etc.
Maybe refer to it all as years? 1 day = 1/365, 1 week = 1/52, and 1 month = 1/12.
The difference is that while 1 - 4 business days would skip weekends and holidays, anything a week longer would be strictly that, measured in weeks or months only, but to be sure the follow-up date ended on a weekday.
How could I modify the solution here to include weeks and months?
https://www.experts-exchange.com/questions/28741399/How-do-you-determine-a-date-after-x-number-of-business-days.html
The interval is coming from a drop down in the cboPriority.Column(2) field.
Me!FollowupDate.Value = DateAddWorkdays(Val(Me!cbo
currently they are all listed as business days
1
2
5 (1 week)
15 (3 weeks)
44 (1 month)
I really don't know what would be a good way to refer to weeks or months while indicating a change in unit of measure from days.
Is it best just to leave it as days? I was just looking for a more precise way that a person could look at the difference between the opening date and the follow up date and say, yes, that is 3 weeks or 2 months between, etc.
Maybe refer to it all as years? 1 day = 1/365, 1 week = 1/52, and 1 month = 1/12.
The difference is that while 1 - 4 business days would skip weekends and holidays, anything a week longer would be strictly that, measured in weeks or months only, but to be sure the follow-up date ended on a weekday.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Ron
That won't work, because one week is not 7 business days, and one month is not 30 business days.
You could multiply by 5, 20, etc, but it still would not be accurate.
That won't work, because one week is not 7 business days, and one month is not 30 business days.
You could multiply by 5, 20, etc, but it still would not be accurate.
Graham, you're right. I was thinking the other way around. However, doesn't your suggestion about using DateAdd exhibit the same problem? When you add 3 ww, you're still adding 21 days and not 15 business days.
Ron
Ron
Yes, but if I want to add, say, one month I want the result to be the same day in the following month (perhaps adjusted to the closest working day) and I don't want the result to be skewed by how many holidays or weekends there are in that month.
If ask for the date 3 weeks from now, I would expect it to give me a Friday (it's Friday where I am :)), and not bump it on to the following Monday if there happened to be a holiday in the way.
If ask for the date 3 weeks from now, I would expect it to give me a Friday (it's Friday where I am :)), and not bump it on to the following Monday if there happened to be a holiday in the way.
As you have about 52 weeks in a year, thus about 260 business days minus some holidays, I would do:
1 week = 5 business days
1 month = 20 business days
1 year = 240 business days
So, if your combo select is 5 weeks and using the previous function:
Days = 5 * 5
DeliveryDate = DateAddWorkdays(Days, StartDate)
This date you could move back or forward to, say, nearest Monday or Friday according to your business rules.
As noted previously, this will calculate in a fraction of a millisecond.
/gustav
1 week = 5 business days
1 month = 20 business days
1 year = 240 business days
So, if your combo select is 5 weeks and using the previous function:
Days = 5 * 5
DeliveryDate = DateAddWorkdays(Days, StartDate)
This date you could move back or forward to, say, nearest Monday or Friday according to your business rules.
As noted previously, this will calculate in a fraction of a millisecond.
/gustav
Going off of Gustav's numbers, here's my revised code:
Dim iNum As Integer
Dim sPeriod As String
Dim daysToAdd As Integer
sPeriod = Me.cboPriority.Column(2)
iNum = Val(sPeriod)
If sPeriod Like "*week*" Then
daysToAdd = iNum * 5
ElseIf sPeriod Like "*month*" Then
daysToAdd = iNum * 20
ElseIf sPeriod Like "*year*" Then
daysToAdd = iNum *240
Else
daysToAdd = iNum
End If
Call AddBusinessDays(Me.OpenDate, daysToAdd)
Ron
ASKER
If I were to follow through with this modification, I would probably do a version where the amounts and the units would be a drop down variable, and then use If statements for the DateAdd. But, that is a lot of work when I think the current days only version will suffice. The other solutions are remakes of days only.
Open in new window
Ron