Simple Excel Question?

[Jo]

Hi everone,

Say I have in one cell this: 1/2/2007 and I want to use function
Right(cell, 5) to extract the year only......

Instead, I get number like 39264?! How can I extract the year from
thing like 1/2/2007?

Thanks,
Mike

 

[Charles May]

=YEAR(cell)

 

[Chip Pearson]

Jo,

The reason you get a number like 39264 is because that is how dates are
stored internally. A date is stored as the number of days since 0-Jan-1900.
1 = 1-Jan-1900, 2 = 2-Jan-1900, ... 39274 = 11-July-2007. When you take the
RIGHT of the cell, you are taking the right characters of the number of the
date serial number, not the formatted date in the cell. This will return a
number like the 39264 you encountered.

To get the year, use the YEAR function. E.g.,

=YEAR(A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[ed.heidicker]

=YEAR(cell)

Go to Cells/Format/Custom and use yyyyy. That will show you only the
year. You can scroll the custom box to see other variations.