Excel - Extract Year Only

Asked By Pam on 17-Oct-08 05:20 PM

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,

Dave Peterson replied on 17-Oct-08 05:23 PM
Make sure you format the cell with the =year() formula as General.

Right now you have it formatted as a date.


Dave Peterson
Mike replied on 17-Oct-08 05:33 PM

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

Pam replied on 17-Oct-08 05:37 PM
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.
Pam replied on 17-Oct-08 05:37 PM
Thank you for your quick reply and helpful answer.
Chip Pearson replied on 17-Oct-08 06:27 PM
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.

Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
(email on web site)

On Fri, 17 Oct 2008 16:20:41 -0500, "Pam" <pamnospam@deltaprocess.com>
Joseph Lauchlan replied to Chip Pearson on 04-Oct-10 03:34 PM

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.