Link to home
Start Free TrialLog in
Avatar of David Bigelow
David BigelowFlag for United States of America

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!cboPriority.Column(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.
ASKER CERTIFIED SOLUTION
Avatar of Graham Mandeno
Graham Mandeno
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't think you have to modify the function at all.  Just convert the period to days before calling the function.  For instance, with the way you have your combobox setup, you can use this 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 * 7
    ElseIf sPeriod Like "*month*" Then
        daysToAdd = iNum * 30
    ElseIf sPeriod Like "*year*" Then
        daysToAdd = iNum * 365
    Else
        daysToAdd = iNum
    End If
        
    Call AddBusinessDays(Me.OpenDate, daysToAdd)

Open in new window

Ron
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.
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
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.
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
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)

Open in new window

Ron
Avatar of David Bigelow

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.