Why doesn't =YEAR(A1) work in my speadsheet

[Guest]

I'm trying to figure out how to extract the year (like 2005) from a
date (like 4/21/2005).

I hunted around on the web and found lots and lots of stuff way too
complicated for me to understand, but finally I found one explanation
that made me actually laugh outloud when I read it because even I could
understand it:

"Lets say that cell A1 contains the date July 21, 2004 (entered as
21/7/2004 or 7/21/2004 depending on your date system). You want to
extract the year (i.e. 2004) from that date and put it in cell C1.
Simply enter the formula =YEAR(A1) in cell C1."*


What could be simpler, right?

So, in my worksheet I have a column of dates, column D

And I've created a new column for the years, column A.

So in cell A658 I entered "=YEAR(D658)" [without the quotes, of
course].

Cell D658 already contained the date 02/08/05.

But after I entered the formula in cell A658, instead of displaying the
year 2005 as I had expected, it instead just displays the formula,
exactly as I typed it: =YEAR(D658)

So, what have I missed? I'm guessing it's something so obvious that as
you are reading this you are cracking up laughing at me. But I don't
mind. All I want is to learn where I messed up, even if I have to end
up looking like a perfect fool in the process.

Will in Seattle
a.k.a. "Clueless"

*text quoted from:
http://www.theexceladdict.com/nlarchive/040721rmg.htm#qt

 

[Niek Otten]

Hi Will,

Probably column A is formatted as text.
Format the column as General or Number and re-enter the formula.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ron Rosenfeld]

But after I entered the formula in cell A658, instead of displaying the
year 2005 as I had expected, it instead just displays the formula,
exactly as I typed it: =YEAR(D658)

So, what have I missed?

Assuming that is the only cell that is showing like this on your worksheet,
then probably somehow you have entered it as TEXT rather than as a formula.

This might be the case if the cell was formatted as TEXT before you entered the
formula; or if you inadvertently entered a leading <space> when you entered the
formula.


--ron

 

[Bob Phillips]

You might also want to check menu Tools>Options>View and ensure that
Formulas checkbox in the Windows Option section is not checked.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

By golly that did the trick!

I thought of that after I had entered the formula, and changed it from
text to general, but I didn't think to re-enter the formula after
changing the formatting.

Many thanks for the help!

Will in Seattle
a.k.a. "Clueless"

P.S. I don't know how to quote the text of the message I'm replying to
using this Google Groups interface, so in the event that it isn't
obviouis from my reply, I'm replying to Niek Ottens suggestion that I
reformat the column to general and then re-enter the formula.