# 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?

The interval is coming from a drop down in the cboPriority.Column(2) field.

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
###### 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.

ConsultantCommented:
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:

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

-- Graham Mandeno [Access MVP since 1996]

Experts Exchange Solution brought to you by

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

Commented:
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

sPeriod = Me.cboPriority.Column(2)
iNum = Val(sPeriod)

If sPeriod Like "*week*" Then
ElseIf sPeriod Like "*month*" Then
ElseIf sPeriod Like "*year*" Then
Else
End If

``````
Ron
ConsultantCommented:
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.
Commented:
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
ConsultantCommented:
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.
CIOCommented:
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

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
Commented:
Going off of Gustav's numbers, here's my revised code:
``````    Dim iNum As Integer
Dim sPeriod As String

sPeriod = Me.cboPriority.Column(2)
iNum = Val(sPeriod)

If sPeriod Like "*week*" Then
ElseIf sPeriod Like "*month*" Then
ElseIf sPeriod Like "*year*" Then