My name is Philipp C. Heckel and I write about nerdy things.

Excel: Add weekdays to date (VBA)


Office

Excel: Add weekdays to date (VBA)


Since many companies work with Microsoft Excel it is often necessary to be inventive in order to implement the required functionality in your worksheet.

In some applications you might want to calculate a deadline date within the week, e.g. to make sure that a product or service will be delivered within a time frame of max. 10 working days.


Contents


1. The VB function

The following small function adds weekdays to a given start date and calculates a dealine date within the week (Mo – Fr):

2. Sample usage

The function above can either be used in a spreadsheet or within a VB script.

2.1. Usage in a spreadsheet

Here’s how you would add it to a spreadsheet, e.g. in Microsoft Excel or in OpenOffice.org Spreadsheet:

A B C
1 Description Order Date Service Level Violation Date (T+10 days)
2 10 workstations 27.07.2008 =AddWeekDays(B2,10)
3 2 multi function printers 27.07.2008 =AddWeekDays(C2,10)

2.2. Usage in a VB script

4 Comments

  1. Gabriel

    Dear Philipp

    Excellent script! It inspired me a lot in finding a good and flexible solution for my requirements. Thanks a lot for posting it.

    Kind regards
    Gabriel


  2. Dennis

    I would recommend using the date as a ‘date’ rather than as a long.

    This enables correct use of the built in function ‘now’, which is rather convenient just as ‘date’ is nice to use.

    What happens now for instance when you use addworkdays(now,1) is that ‘now’ is rounded to the nearest day, so potentially tomorrow, causing errors.


  3. Jose Ramirez

    Philipp
    I really apprecite your support posting this formula, it is going to helpe a lot in my daily tasks.

    Thanks again and all the best for you
    Jose Ramirez
    Mexico


  4. Christine Glatz

    This doesn’t seem to work when you want to add a negative date. For example, what is 7 days prior to the Go Live date. I tried passing -7 as the interval, which is what you do for the regular DateAdd().