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.
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):
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
AddWeekDays = d
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:
|1||Description||Order Date||Service Level Violation Date (T+10 days)|
|3||2 multi function printers||27.07.2008||=AddWeekDays(C2,10)|
2.2. Usage in a VB script
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
I'd very much like to hear what you think of this post. Feel free to leave a comment. I usually respond within a day or two, sometimes even faster. I will not share or publish your e-mail address anywhere.