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?
http://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.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
Hi David

If you are adding weeks, months, years, etc then you really don't care how many non-business days are in the interim.  One month from today will be 16 November regardless.

Therefore you can simply use DateAdd - for example:

DateAdd( "ww", 3, Me!OpenDate.Value ) ' to add 3 weeks
DateAdd( "m", 2, Me!OpenDate.Value ) ' to add 2 months
DateAdd( "yyyy", 1, Me!OpenDate.Value ) ' to add 1 year

If necessary, you could check that the resulting date is a business day and adjust it as required.

-- Graham Mandeno [Access MVP since 1996]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
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
0
GrahamMandenoCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

IrogSintaCommented:
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
0
GrahamMandenoCommented:
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.
0
Gustav BrockCIOCommented:
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
0
IrogSintaCommented:
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
0
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.