Tutorial :Syntax of DateAdd



Question:

I am trying to use Excel the DateAdd function to find the next working day.

It provides the same result whether you use d, w or y for the period argument.

I tried the following code, and get identical results in all 3 columns.

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


Solution:1

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  


Solution:2

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.


Solution:3

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


Solution:4

DateAdd("w" doesn't work as expected - Microsoft acknowledged it and posted a workaround at https://support.microsoft.com/en-us/kb/115489


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »