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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:48:24  来源:igfitidea点击:

Syntax of DateAdd

excelvbaexcel-vba

提问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 上发布了解决方法