vba DateAdd 的语法
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1432746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax of DateAdd
提问by Patrick Honorez
I am trying to use Excel the DateAdd function to find the next working day.
我正在尝试使用 Excel 的 DateAdd 函数来查找下一个工作日。
It provides the same result whether you use d, w or y for the period argument.
无论您对 period 参数使用 d、w 还是 y,它都提供相同的结果。
I tried the following code, and get identical results in all 3 columns.
我尝试了以下代码,并在所有 3 列中获得相同的结果。
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
结果:
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/01/2009/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/2
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
...
回答by Oorang
Despite the confusing language of the DateAdd documentation. DateAdd does notadd workdays. "W" will only add nnumber 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.
尽管 DateAdd 文档的语言令人困惑。使用DateAdd并没有增加工作日。“W”只会增加n天数(如您所见)。您可以滚动自己的函数,也可以执行以下操作: 在 Excel 中,转到工具>加载项并打开 VBA 分析工具包。在 VBE 中,转到 Tools>References 并设置对 atpvbaen.xls 的引用。现在您可以在 VBA 中使用 WorkDay 函数了。
Public Sub Test()
MsgBox CDate(Workday(Date, 3))
End Sub
回答by JohnFx
It is "ww" for week and "yyyy" for year.
周为“ww”,年为“yyyy”。
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 函数的语法是:
DateAdd ( interval, number, date )
interval is the time/date interval that you wish to add. It can be one of the following values:
interval 是您要添加的时间/日期间隔。它可以是以下值之一:
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.
number 是您要添加的间隔数。
date is the date to which the interval should be added.
date 是应该添加间隔的日期。
回答by Buggabill
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.
您的示例会将相同的数字添加到今天的三个不同版本中。“d”代表当前日期。"w" 表示星期几,例如 1 代表星期日(默认)。“y”代表一年中的某一天。9 月 16 日是今年 365 天的第 259 天。
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 herefor some workday math
编辑:看看这里的一些工作日数学
回答by ohboy
DateAdd("w" doesn't work as expected - Microsoft acknowledged it and posted a workaround at https://support.microsoft.com/en-us/kb/115489
DateAdd("w" 没有按预期工作 - 微软承认并在https://support.microsoft.com/en-us/kb/115489 上发布了解决方法