Year formula problems!

[PUVSTER]

I am trying to extract a year from the date formay dd/mm/yyyy and no matter
what I do it seems to give me another date format but in 1905 and not 2009? I
have tried to copy and paste and change date format and used the =A1*1
formula but I am really confused, I have to get this done by tommorrow and my
head is hurting!

Help would be much appreciated!

 

[Bernard Liengme]

=YEAR(A1)
best wishes

 

[PUVSTER]

Thanks,

Tried this and still returns the 1905 full date e.g. Date in A1=03/01/2009
and using year(A1) returns 01/07/1905.

 

[Bernard Liengme]

Excel stores dates as a serial number starting 1 Jan 1900
The formula =YEAR(A1) is returning the number 2009 but you have that cell
formatted as data and the day 2009 after 1/Jan/1900 was 1 July 1905

So just click the cell, and format it Genial to show 2009

BTW: when people have dates such as 01/07/1905 in a message it would help to
know what date-form is being used: American mm/dd/yyyy or rest-of-world
dd/mm/yyyy. I surmised that you use dd/mm/yyyy as do I.

best wishes

 

[PUVSTER]

Fantastic, sorted! I did put the date format in my original post but hey all
done now!

 

[Bernard Liengme]

Genial is a new Excel 2009 format; it is kinder than General <grin>
Oh the wonders of automatic spell check coupled with poor eyesight!

 

[tim benjamin]

This was driving me nuts. Thanks for your help. In case someone else is reading this and didn't follow,

Cell A1 = 2009/03/01

Cell C1 = YEAR(A1) :result = 1905 argh!

If you use year() on cell A1, you will get back a number representing the year equivalent of cell A1, the PROBLEM YOU ARE HAVING is that the formating for cell C1 is set to date! Change cell C1! Do it now!

I know this is the same thing as the prior post, but I read it once without quite grasping which cell had the formating problem. Thanks upstate New York education system.

//tim

 

[Stan Brown]

This was driving me nuts. Thanks for your help. In case someone else is reading this and didn't follow,

And now it drives the rest of us nuts. DON'T change the subject line
when posting a follow-up. And DO trim out all the stuff in the
quoted article that you're not directly responding to.

 

[The Chief Instigator]

And now it drives the rest of us nuts. DON'T change the subject line
when posting a follow-up. And DO trim out all the stuff in the
quoted article that you're not directly responding to.

I've got a few hockey league spreadsheets, and I enter the dates in
yyyy/mm/dd format (which makes sorting dates much easier).