2 Business Days from date entered

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

    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.

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

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

    Bruce :cool:

  • 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!

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


    Bruce :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!