Excel: Add weekdays to date (VBA)

Jul 29 / 2008
4

Uncategorized

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

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

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

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

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

4 Comments

Gabriel August 28th, 2012

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
Dennis April 6th, 2013

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.
Jose Ramirez November 18th, 2013

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
Christine Glatz November 20th, 2013

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().