在Excel中从VBA中提取年份 [英] Extract Year from Date Using VBA in Excel

问题描述

我有一个excel工作簿与几张。我需要做的是将年份从一张表中的列转移到另一张表。我可以得到列转移罚款，但我不能让它给我只是日期。

日期在 dd-mon-yy 格式（例如 14-Jul -14 ）。我需要的是在 yyyy （例如 2014 ）中获取。任何帮助都会很棒。

我正在尝试的代码是这样，但它不起作用。

  [...] 
对于I = 5到5 
如果不是TransferCol（5）= 0然后
工作表（结果）。单元格（Row，StartColumn + I）=工作表（程序）。单元格（RowTransfer，Year（TransferCol（5）））
退出Do 
结束如果
下一个
 [....] 
  

解决方案

在我看来，有（至少）有2种方式来实现你想要的。

1。格式化

而不是从源日期提取年份，您可以复制整个日期并格式化目标单元格，仅显示年份。通过这种方式，您可以保留原始日期信息，但仅显示您感兴趣的部分。以下示例将日期从Sheet1/ Cell（1,1）到Sheet2/ Cell（1,1），并使用 NumberFormat 将目标单元格格式设置为只显示4位数的年份部分：

  Public Sub test1（）
 
 Dim rSrc As Range 
 Dim rDst As Range 
 
设置rSrc = Sheets（Sheet1）。单元格（1，1）
设置rDst = Sheets（Sheet2）。 ）
 
 rDst = rSrc 
 rDst.NumberFormat =yyyy
 
 End Sub 
  

2。使用 Year（）函数

在这种情况下，您需要注意的是确保目标单元格格式化为（整数）数字。如果将其格式化为日期，则结果将非常错误，因为它将使用年值作为与1901年或以后的日期偏移量。代码本身很简单。我使用前面的例子作为这个基础：

  Public Sub test2（）
 
 Dim rSrc As Range 
 Dim rDst As Range 
 
设置rSrc = Sheets（Sheet1）。单元格（1，1）
设置rDst = Sheets（Sheet2）。单元格（1，1）
 
 rDst =年（rSrc）
 
 End Sub 
  

这两个例子都是简单的，但我希望他们给你一般的想法。我相信你可以根据自己的要求进行调整。

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.

The date is in dd-mon-yy format (ex. 14-Jul-14). What I need is to get it in yyyy (ex. 2014). Any assistance would be great.

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

解决方案

It seems to me there are (at least) 2 ways to achieve what you want.

1. Formatting

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 NumberFormat to set the destination cell formatting to only show the 4-digit year part:

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

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:

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.

这篇关于在Excel中从VBA中提取年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！