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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Function AddWeekDays(StartDate As Long, Days As Long) As Date Dim i As Long Dim d As Date d = StartDate i = 0 While i < Days d = DateSerial(Year(d), Month(d), Day(d) + 1) If Weekday(d, vbMonday) < 6 Then i = i + 1 End If Wend AddWeekDays = d End Function |
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
|
1 2 3 4 5 6 7 |
Sub TestWeekDays() Dim SLA_Violation_Date As Date SLA_Violation_Date = AddWeekDays(DateValue("27.07.2008"), 10) ' Result will be 27.07.2008 + 10 Working Days = 08.08.2008 MsgBox SLA_Violation_Date End Sub |
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
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.
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
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().