Excel: add weekdays to date (VBA)

Posted in Office on July 29th, 2008 by Philipp C. Heckel – Be the first to comment

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.

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

Sample usage

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

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)

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

Tags: , ,

This entry was posted on Tuesday, July 29th, 2008 at 4:07 pm and is filed under Office. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply