Extract Year from Date Using VBA in Excel

Question

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

Answer 1

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.