Incorrect Serial Number Returned for YEAR Function

**greenmat** · 07-26-2012, 08:38 PM

Hello,

I exported data from a database, and I'm trying to convert a date, 05/03/2011 into a column with the YEAR, which should read 2011. I have inserted a column, converted the date to a customer YYYY field, performed a copy and paste special with values only, but it returns the full date, not just the year. I have also tried the year function, =YEAR(ref cell); 05/03/2011 is reuturning 1905. I'm trying to have a simple 2011 returned so I can run the data in a pivot table. Happy to go about this a different way.

Any help would be greatly appreciated.

**FDibbins** · 07-26-2012, 08:44 PM

have you tried to just simply format the data as year only, so that only year year shows? the cell contents will still contain the full date, but will only show the year (or am i missing your question?)

**MarvinP** · 07-26-2012, 08:48 PM

Hi greenmat and welcome to the forum.

This feels like a Apple vs PC date difference. Read http://support.microsoft.com/kb/180162
Did the file come from a Mac? They keep dates differently.

To get farther we need a sample workbook showing the problem. My guess is the number isn't a real date but need the example to confirm.

**greenmat** · 07-26-2012, 09:05 PM

Hello FDibbins and Marvin,

The pivot table requires a year date, not just formatting or it will list each date entry separately. My tricks listed above usually do the trick.

I exported the data from a website, so it may be in text or cvs format, and not being ready as a numeral (at least that is my guess).

Here are some sample entries ("sales date" is the actual data; "sale year" is formatting to YYYY; "sale yr 1" is the function =YEAR(ref cell)),

Sale Date Sale Year Sale Yr 1
5/3/2011 2011 1905
7/18/2011 2011 1905
5/3/2011 2011 1905
4/21/2011 2011 1905
4/24/2012 2012 1905

I can send a worksheet, I just need a little guidance on this....sorry I'm new here.

**MarvinP** · 07-26-2012, 09:08 PM

To attach a sample workbook, click on the "Go Advanced" below the message area. Then on the Paper Clip Icon above the advanced message area and go from there.

**greenmat** · 07-26-2012, 09:23 PM

Thanks for the help!

**Haseeb Avarakkan** · 07-26-2012, 09:35 PM

Change the column C format to general or Number. It is currently formatted as yyyy

**greenmat** · 07-26-2012, 09:53 PM

Thanks!

**Cutter** · 07-27-2012, 06:22 PM

@ greenmat

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.