Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Extract Year Only

8,804 views
Skip to first unread message

Pam

unread,
Oct 17, 2008, 5:20:41 PM10/17/08
to
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


Dave Peterson

unread,
Oct 17, 2008, 5:23:31 PM10/17/08
to
Make sure you format the cell with the =year() formula as General.

Right now you have it formatted as a date.

--

Dave Peterson

Mike H

unread,
Oct 17, 2008, 5:33:01 PM10/17/08
to
Hi

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


Mike

Pam

unread,
Oct 17, 2008, 5:37:32 PM10/17/08
to
Thank you for your quick reply and helpful answer.
"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message
news:48F90253...@verizonXSPAM.net...

Pam

unread,
Oct 17, 2008, 5:37:12 PM10/17/08
to
I don't know what I was doing wrong, but I thought yyyy would work and it
didn't, but entered exactly as you have and it did.
Thanks for the quick reply and helpful answer.

"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:FC516604-403E-4360...@microsoft.com...

Chip Pearson

unread,
Oct 17, 2008, 6:27:47 PM10/17/08
to
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" <pamn...@deltaprocess.com>
wrote:

Joseph Lauchlan

unread,
Oct 4, 2010, 3:35:02 PM10/4/10
to
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.

> On Friday, October 17, 2008 5:20 PM Pam wrote:

> 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


>> On Friday, October 17, 2008 5:23 PM Dave Peterson wrote:

>> Make sure you format the cell with the =year() formula as General.
>>
>> Right now you have it formatted as a date.
>>
>> Pam wrote:
>>

>> --
>>
>> Dave Peterson


>>> On Friday, October 17, 2008 5:33 PM Mike wrote:

>>> Hi
>>>
>>> with your date in a1
>>> put this in b1
>>> =A1 and a custom format of yyyy
>>>
>>>
>>> Mike
>>>
>>> "Pam" wrote:


>>>> On Friday, October 17, 2008 5:37 PM Pam wrote:

>>>> 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.


>>>>> On Friday, October 17, 2008 5:37 PM Pam wrote:

>>>>> Thank you for your quick reply and helpful answer.


>>>>>> On Friday, October 17, 2008 6:27 PM Chip Pearson wrote:

>>>>>> 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" <pamn...@deltaprocess.com>
>>>>>> wrote:


>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>> Review of DevExpress DXperience Control Suite
>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/f8ce82a9-d5c2-44fc-91af-e67df5ae502f/review-of-devexpress-dxperience-control-suite.aspx

Gord Dibben

unread,
Oct 4, 2010, 4:02:49 PM10/4/10
to
A1 = 1/15/2006 which is a valid date with serial number of 38722

=YEAR(A1) = 2006

=TEXT(YEAR(2006),"yyyy) = 6/28/1905 because serial number 2006 is 6/28/1905

Simply enter =YEAR(A1) to get 2006..........format as General


Gord Dibben MS Excel MVP

Pete_UK

unread,
Oct 4, 2010, 4:58:19 PM10/4/10
to
If you really want the year as a text value you can do this:

=text(A2; "yyyy")

or you could do this (following on from Gord):

=year(A2)&""

Hope this helps.

Pete

On Oct 4, 8:35 pm, Joseph Lauchlan <josephedwardlauch...@gmail.com>
wrote:

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


> >>>>>> wrote:
> >>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
> >>>>>> Review of DevExpress DXperience Control Suite

> >>>>>>http://www.eggheadcafe.com/tutorials/aspnet/f8ce82a9-d5c2-44fc-91af-e...- Hide quoted text -
>
> - Show quoted text -

alang...@aol.com

unread,
Oct 4, 2010, 5:56:07 PM10/4/10
to
On Oct 4, 8:35 pm, Joseph Lauchlan <josephedwardlauch...@gmail.com>
wrote:

> 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.

As with many languages date-time is formated as the integer being the
number of days from some standard (usually 31 Dec 1899), and the
decimal part being the proportion of the day which is the time (ie 0.5
is 12.noon). the Date can be the integer part with the decimal part
being 0.00.

You have been just adding integers to the date value & hence get a
date 2,005 days leter than the start date.

Hence to get the date for calculations you should use INT(<date-
time>), where <date-time> could be NOW().

To display a cell as the year of a date, or the date as a year, you
just need to format the cell appropriately, Select the cell (or
cells). Right-click on the cell(s) and select Format Cells from the
menu. Select the Number tab and Custom in the list. Then enter YYYY in
the edit box. The cell(s) will then display only the year of whatever
value is in the cell(s).

You may want to display the full date in one cell & only the year in
the next cell. You will then need to copy the date cell into the year
cell (by '=A4' for example) before formatting the year cells as I
described.

Note that the format string is a very useful tool to use. It can
contain all sorts of letters to denote content. It can also be figured
into three parts separated by semi-colons. the part the control the
formats for <positive numbers>;<negative numbers>;<zero>. Very useful
in making a user friendly display.

Alan Lloyd

0 new messages