Excel - Extract Year Only

Hi,

I found a post that gave year function (=year(cell)) to extract only the
year from a date.  I have 1/15/2006 and I get 6/28/1905.  This date was
exported from Access into an Excel spreadsheet. I'm not sure if that would
have an effect on it, but thought I would include it in message.  If I
format the new column to a number, I get 38722.  I know Access uses numbers
for dates, so I need to somehow convert to get correct year.

Can anyone help me get 2006 from 1/15/2006?

Thanks in advance for any help,
Pam

Make sure you format the cell with the =year() formula as General.

Right now you have it formatted as a date.


--

Dave Peterson

Hi

with your date in a1
put this in b1
=A1 and a custom format of yyyy


Mike

I do not know what I was doing wrong, but I thought yyyy would work and it
did not, but entered exactly as you have and it did.
Thanks for the quick reply and helpful answer.

Thank you for your quick reply and helpful answer.

Dates in Excel are stored as the number of days since 0-Jan-1900 (1 =
1-Jan-1900 through 39738 = 17-Oct-2008 and so on). When you use YEAR,
you return to the cell the year as an integer, say 2006. If you format
that cell as a date, Excel treats the value as a serial date, and 2006
days since 0-Jan-1900 is 28-June-1905. You need to format that cells
as General or numeric, not as a date.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 17 Oct 2008 16:20:41 -0500, "Pam" <pamnospam@deltaprocess.com>

Chip,



Your explanation makes sense that excel records dates as an integer.  However, I don't understand what to do about it.  I have dates (dd/mm/yyyy) in column A and year, with the formula =text(year(A2); "yyyy"), in column C.  Dates have the date format and years have a generic format.  Changing the format of dates does not affect the year.  It still appears 1905.  Any advice?  Thanks.