vba 如何在 Excel 2013 的列中的每个日期字段中添加一个月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21495284/
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
How do I add one month to each date field in a column in Excel 2013
提问by Scott
I have a column of date fields that contain different dates. I want to add 1 month to each of the dates.
我有一列包含不同日期的日期字段。我想为每个日期添加 1 个月。
So if my column/row is the following
所以如果我的列/行如下
A3: 1/2/2014
A4: 1/4/2014
A5: 1/10/2014
A6: 1/15/2014
Whatever formula or method I run will change everything to
我运行的任何公式或方法都会将所有内容更改为
A3: 2/2/2014
A4: 2/4/2014
A5: 2/10/2014
A6: 2/15/2014
回答by mscoon
You can use the EDATE function which adds a number of months to a give date.
您可以使用 EDATE 函数，该函数为给定日期添加了几个月。
EDATE(A1, 1)
will add one month to the date in cell A1.
将向单元格 A1 中的日期添加一个月。
回答by Siddharth Rout
That won't do what I need it to do though. I want to update the existing cells to increase the date in each of them. I updated my question to (hopefully) make it clearer. – Scott 33 mins ago
但这不会做我需要它做的事情。我想更新现有单元格以增加每个单元格中的日期。我更新了我的问题（希望）使其更清楚。– 斯科特 33 分钟前
From the comment under the deleted answer(Jerry) (Since I can still see them :p), I guess you want to use VBA. If that is the case then see this.You need to use the DateAdd()
从已删除答案（Jerry）下的评论（因为我仍然可以看到它们：p），我猜您想使用VBA。如果是这种情况，那么请参阅此内容。您需要使用DateAdd()
If you check the Excel's help, DateAddreturns a Variant (Date) containing a date to which a specified time interval has been added.
如果您查看 Excel 的帮助，DateAdd将返回一个包含添加了指定时间间隔的日期的 Variant (Date)。
Syntax
句法
DateAdd(interval, number, date)
DateAdd（间隔，数字，日期）
The interval argument has these settings:
interval 参数具有以下设置：
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Paste this in a module.
将其粘贴到模块中。
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, i As Long
'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 3 To lRow
.Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
Next i
End With
End Sub
回答by pnuts
Since in your example all your dates are in January, this should work: enter 31in some cell and copy. Select your dates and Paste Special… Add. Simple, but big disadvantage is that this only works for 7 months of the year!
由于在您的示例中所有日期都在 1 月，因此这应该有效：输入31某个单元格并复制。选择您的日期和选择性粘贴... 添加。简单但很大的缺点是，这只适用于一年中的 7 个月！
