Excel DateAdd not working properly ?

Question

I am trying to use Excel the DateAdd function to find the next working day. It seems that Excel is not working as expected with this function, and always provides the same result wether you use d, w or y for the period argument. (by the way the explanations of the "y" value are beyond my logic).

I tried the following code, and get identicial results in all 3 columns. Am I mad ?

Sub test()
    Dim i As Integer
    For i = 1 To 9
        Debug.Print i;
        Debug.Print DateAdd("d", i, #9/10/2009#);
        Debug.Print DateAdd("w", i, #9/10/2009#);
        Debug.Print DateAdd("y", i, #9/10/2009#)
    Next i
End Sub

Result:
1 11/09/2009 11/09/2009 11/09/2009
2 12/09/2009 12/09/2009 12/09/2009
3 13/09/2009 13/09/2009 13/09/2009
4 14/09/2009 14/09/2009 14/09/2009
5 15/09/2009 15/09/2009 15/09/2009
6 16/09/2009 16/09/2009 16/09/2009
7 17/09/2009 17/09/2009 17/09/2009
8 18/09/2009 18/09/2009 18/09/2009
9 19/09/2009 19/09/2009 19/09/2009

Extract from the documentation: Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax  
DateAdd(interval, number, date)  
...  
interval Required. String expression that is the interval of time you want to add.   
....  

The interval argument has these settings:  

Setting Description   
yyyy Year   
q Quarter   
m Month   
y Day of year   
d Day   
w Weekday   
ww Week   
...

Answer 1

It is "ww" for week and "yyyy" for year.

Corrected code:

Sub test()
    Dim i As Integer
    For i = 1 To 9
        Debug.Print i;
        Debug.Print DateAdd("d", i, #9/10/2009#);
        Debug.Print DateAdd("ww", i, #9/10/2009#);
        Debug.Print DateAdd("yyyy", i, #9/10/2009#)
    Next i
End Sub

From the documentation:

The syntax for the DateAdd function is:

DateAdd ( interval, number, date )

interval is the time/date interval that you wish to add. It can be one of the following values:

Value   Explanation
yyyy    Year
q   Quarter
m   Month
y   Day of the year
d   Day
w   Weekday
ww  Week
h   Hour
n   Minute
s   Second

number is the number of intervals that you wish to add.

date is the date to which the interval should be added.

Answer 2

Your example will add the same number to three different versions of today. "d" represents the current date. "w" represents the day of the week such as 1 for Sunday(default). "y" represents the day of the year. Sept 16 is day 259 of 365 this year.

In order to get what it looks like you're going for do this:

Sub test()
    Dim i As Integer
    For i = 1 To 9
        Debug.Print i;
        Debug.Print DateAdd("d", i, #9/10/2009#)    ' adds i days
        Debug.Print DateAdd("ww", i, #9/10/2009#)   ' adds i weeks
        Debug.Print DateAdd("yyyy", i, #9/10/2009#) ' adds i years
    Next i
End Sub

Outputs:

 1 9/11/2009 9/17/2009 9/10/2010 
 2 9/12/2009 9/24/2009 9/10/2011 
 3 9/13/2009 10/1/2009 9/10/2012 
 4 9/14/2009 10/8/2009 9/10/2013 
 5 9/15/2009 10/15/2009 9/10/2014 
 6 9/16/2009 10/22/2009 9/10/2015 
 7 9/17/2009 10/29/2009 9/10/2016 
 8 9/18/2009 11/5/2009 9/10/2017 
 9 9/19/2009 11/12/2009 9/10/2018

EDIT: Take a look here for some workday math

Answer 3

Despite the confusing language of the DateAdd documentation. DateAdd does not add workdays. "W" will only add n number of days (as you discovered). You can either roll your own function, or do the following: In Excel, go to Tools>Add Ins and turn on the Analysis Tool Pack for VBA. In the VBE, go to Tools>References and set a reference to atpvbaen.xls. Now you can use the WorkDay function in VBA.

Public Sub Test()
    MsgBox CDate(Workday(Date, 3))
End Sub