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.

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
        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:

2.2. Usage in a VB script

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