在 Excel 中使用 VBA 从日期中提取年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24737099/
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
Extract Year from Date Using VBA in Excel
提问by Mechanical Notaprogrammer
I have an excel workbook with several sheets. What I need to do is transfer the year from a column in one sheet to another sheet. I can get the column to transfer over fine, but I can't get it to give me just the date.
我有一个包含多张工作表的 Excel 工作簿。我需要做的是将年份从一张纸中的一列转移到另一张纸上。我可以让专栏顺利转移，但我不能让它只给我日期。
The date is in dd-mon-yyformat (ex. 14-Jul-14). What I need is to get it in yyyy(ex. 2014). Any assistance would be great.
日期采用dd-mon-yy格式（例如14-Jul-14）。我需要的是把它yyyy放进去（例如2014）。任何帮助都会很棒。
The code I'm attempting is this, but it doesn't work.
我正在尝试的代码是这样的，但它不起作用。
[...]
For I=5 to 5
If Not TransferCol (5) = 0 Then
Worksheets ("Results").Cells.(Row, StartColumn + I) = Worksheets("Program").Cells(RowTransfer, Year (TransferCol (5)))
Exit Do
End If
Next
[....]
采纳答案by djikay
It seems to me there are (at least) 2 ways to achieve what you want.
在我看来，有（至少）两种方法可以实现你想要的。
1. Formatting
1. 格式化
Instead of extracting the year from the source date, you could copy the entire date and format the target cell to only show the year. This way you get to keep the original date information but only show the part you're interested in. Here's an example to copy the date from "Sheet1"/Cell(1,1)to "Sheet2"/Cell(1,1)and use NumberFormatto set the destination cell formatting to only show the 4-digit year part:
您可以复制整个日期并将目标单元格的格式设置为仅显示年份，而不是从源日期中提取年份。通过这种方式，您可以保留原始日期信息，但只显示您感兴趣的部分。 下面是一个示例，用于将日期从"Sheet1"/Cell(1,1)to复制"Sheet2"/Cell(1,1)并用于NumberFormat将目标单元格格式设置为仅显示 4 位年份部分：
Public Sub test1()
Dim rSrc As Range
Dim rDst As Range
Set rSrc = Sheets("Sheet1").Cells(1, 1)
Set rDst = Sheets("Sheet2").Cells(1, 1)
rDst = rSrc
rDst.NumberFormat = "yyyy"
End Sub
2. Using the Year()function
2. 使用Year()功能
What you need to be aware of in this case is to ensure the target cell is formatted as an (integer) number. If you format it as a date, then the result will be very wrong, as it will use the year value as a date offset from the year 1901 or thereabouts. The code itself is simple. I'm using the previous example as a basis for this:
在这种情况下，您需要注意的是确保目标单元格的格式为（整数）数字。如果将其格式化为日期，那么结果将是非常错误的，因为它将使用年份值作为从 1901 年左右开始的日期偏移量。代码本身很简单。我使用前面的例子作为这个基础：
Public Sub test2()
Dim rSrc As Range
Dim rDst As Range
Set rSrc = Sheets("Sheet1").Cells(1, 1)
Set rDst = Sheets("Sheet2").Cells(1, 1)
rDst = Year(rSrc)
End Sub
Both examples are simplistic, but I hope they give you the general idea. I'm sure you can adapt them to your requirements.
这两个例子都很简单，但我希望它们能给你一个大致的概念。我相信您可以根据自己的要求调整它们。
回答by Chris Lam
According to your code you are already using the Year() function.
根据您的代码，您已经在使用 Year() 函数。
But i think there is a syntax error:
但我认为存在语法错误：
Worksheets ("Results").Cells.(
There's an extra dot.
有一个额外的点。
回答by MP24
You are using the Cellsfunction wrongly, it seems:
您Cells错误地使用了该功能，似乎：
Worksheets ("Results").Cells.(Row, StartColumn + I) = Worksheets("Program").Cells(RowTransfer, Year (TransferCol (5)))
^^^^^^^^^^^^^^^^^^^^^^^^
What this does is extract the year from TransferCol(5)and access the cell value (RowTransfer, extracted year)of the Programworksheet.
这样做是从提取的一年TransferCol(5)，并访问单元格的值(RowTransfer, extracted year)的的Program工作表。
You may want to do something like
你可能想做类似的事情
Worksheets ("Results").Cells.(Row, StartColumn + I) = Year(TransferCol(5))
if TransferColcontains your date values.
如果TransferCol包含您的日期值。
回答by Alexander Bell
In order to 'Extract Year from Date Using VBA in Excel' (answering your verbatim question) you can use a Year()function of VBA. The alternative will be using DatePartand specifying DateInterval.Yearfor the Interval argument (re: http://msdn.microsoft.com/en-us/library/88k2aec8%28v=vs.90%29.aspx)
为了“在 Excel 中使用 VBA 从日期中提取年份”（回答您的逐字逐字问题），您可以使用Year()VBA的功能。替代方法将使用DatePart并为 Interval 参数指定DateInterval.Year（re：http: //msdn.microsoft.com/en-us/library/88k2aec8%28v=vs.90%29.aspx）
