Date to Year in Excel

Started by Heather Reetz, April 03, 2014, 12:58:45 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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
Yes, there really is a Kalamazoo!

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

:o
Heather Reetz
Yes, there really is a Kalamazoo!

Charlie Charbonneau

try...

=TEXT(YEAR(A1),"0000")
Charlie Charbonneau
GBMB Insurance
San Antonio TX.

EPIC 2022, CSR24, Windows 2012 Hyper-V & 2016, Win10/11 Pro Stations, Sophos Anti-Virus.
.                .                 ..              ...

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)


Billy Welsh
VP of Accounting
CableSouth Media, LLC dba SwyftConnect

Steven Strauss

If the cell with the date is A1, then it is =text(a1,"YYYY")
Steven Strauss - CFO
NIP Group, Inc.  Woodbridge, NJ
Epic 2022 R2
CSR24, Salesforce, Cognos Finance, TM1

Heather Reetz

Thanks, everybody!

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

Heather Reetz
Yes, there really is a Kalamazoo!

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.
Fantasy is more important than knowledge, because knowledge has its boundaries - Albert Einstein