Date to Year in Excel

[Heather Reetz]

I figured out how to custom format the cells to only show me the date.  But... Is there a way to copy or convert the contents of the cell to actually only be the year?  For example, the contents of the cell are 3/9/2009.  With the custom formatting, it shows up in the cell as "2009".  I want to copy it into another cell so that the content of the cell will actually be 2009.

I'm trying to use a pivot table to summarize by year.  Since the actual content is more than the year, I'm getting multiple "2009s" when what I want is for all of the 2009s to be together, all of the 2010s to be together, etc.

[Heather Reetz]

So I found that when I use the YEAR function, really weird stuff happens.

C2 content is 3/9/2009
In D2 I entered =YEAR(C2)
Now D2 shows 1905

[Charlie Charbonneau]

try...

=TEXT(YEAR(A1),"0000")

[Billy Welsh]

Here's how I got it to work:

{CELL S552}       =TEXT(B552,"mm/dd/yyyy")  (Converts numerical date to text in the specified format)

then =RIGHT(S552,4)  (yielded just the year in this cell)

[Steven Strauss]

If the cell with the date is A1, then it is =text(a1,"YYYY")

[Heather Reetz]

Thanks, everybody!

=TEXT(C2,"yyyy") worked for me.  Then I copied and pasted values only. 

[Hans Manhave]

Next time, when you have a column like that, that is actually text instead of a number, use the Text-to-columns and turn it into a date.  You don't have to do anything, just selecting text-to-columns and done.  then format as a date just so you can look at it in pretty format, refresh the pivot table (!), save and exit Excel.  Reopen Excel and you should be able to summarize by year or month or quarter or whatever, in the pivot table.  You don't always have to exit Excel, but I found that to work most of the time.  Usually if you correct the text into a date before creating the pivot table it will work fine too.