2 Business Days from date entered

Qwest336 · May 13th 2016

Good morning all,

VBA Novice here!

I'm making a userform that returns an email using the "With Outmail" with statement to send .HTMLbody code as a message. In the first line of the subject, I want to inform the submitter that we will respond to their request by close of business on the 2nd business day from the submission date. However, the current "code" I used doesn't account properly for business days.

Can anyone help me modify the code so that it accounts for business days? This is part of the Click event procedure for a "Submit" button where "tbTodaysDate" is a locked text box field that auto-populates today's date..

Code

Dim LDate As Date
    LDate = DateAdd("d", 2, EscalationsForm.tbTodaysDate.Value)


'More Coding for the outlook email


'Picks up here


    With OutMail
        .HTMLbody = "We will respond to this Escalation by close of business on " & Format(LDate, "Long Date") & "."

Display More

Any help is greatly appreciated as I've seen some answers using functions but I don't know how to properly deploy them, nor do I understand what they are doing.

skywriter · May 13th 2016

Re: 2 Business Days from date entered

I'm not real experienced in Outlook coding, but there are 2 functions in Excel that you might consider.

Depending on your version of Excel you may have them.

They are Workday and Workday.Intl and they are also available in VBA.

There's a search box in the visual basic editor, you can look it up there and/or you should be able to google them for more information on their usage.

The code below returns next Tuesday May 17th as my weekends are set for Saturday and Sunday.

Date in the arguments is a function that returns today and the 2 is for 2 workdays from today.

Code

Sub Test()
Dim x As Date
x = WorksheetFunction.WorkDay(Date, 2)
End Sub

Qwest336 · May 13th 2016

Re: 2 Business Days from date entered

Thank you skywriter!

That worked perfectly! Since we are solely US-based, I think I can put off worry about Weekends being set differently.

Thanks, again!

skywriter · May 13th 2016

Re: 2 Business Days from date entered

My pleasure, thanks for the feedback. :cool:

You should check out workday.intl for future reference.

You have several choices for the weekend settings and if you want you can customize it for different numbers of days on and off, not just the 2 days off and 5 on.

It's good for the 4x10 hours scenario.

It's a great function.

Workday.Intl

2 Business Days from date entered

Participate now!

Share