=YEAR(3/7/2007) in EXCEL, get the answer as 1900. Why?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LadyCat - 07 Mar 2007 17:02 GMT

I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Sandy Mann - 07 Mar 2007 17:13 GMT

Enclose the date quotes:

=YEAR("3/7/2007")

Otherwise you are saying Year( 3 divided by 7 divided by 2007)

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

>I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Beege - 07 Mar 2007 17:15 GMT

> I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Ladycat,

Put the date in quotes?

Beege

Reply to this Message

Ron Coderre - 07 Mar 2007 17:28 GMT

Excel is actually performing the division indicated by 3/7/2007 to return
the numeric value of: 0.000213538330130258
which, as a date/time value is roughly 18 seconds into 01-JAN-1900

To indicate to Excel that you want the value converted to a date, use this
technique:
=YEAR(--"3/7/2007")

Excel will correctly interpret that you want to convert the text "3/7/2007"
into the date represented.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Ron Coderre - 07 Mar 2007 17:36 GMT

The other responders are correct in omitting the dbl-neg sign (--)....It's
not necessary because the YEAR function forces the conversion from text to
date.

***********
Regards,
Ron

XL2002, WinXP

> Excel is actually performing the division indicated by 3/7/2007 to return
> the numeric value of: 0.000213538330130258
[quoted text clipped - 15 lines]
>
> > I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Dave F - 07 Mar 2007 18:30 GMT

Isn't this whole question obviated by just entering the date in a cell and
referencing that cell in the formula? =YEAR(A1) works fine if A1 contain
3/7/2007.

Dave

Signature

A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

> The other responders are correct in omitting the dbl-neg sign (--)....It's
> not necessary because the YEAR function forces the conversion from text to
[quoted text clipped - 25 lines]
> >
> > > I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Ron Coderre - 07 Mar 2007 18:33 GMT

Probably: Yes.....provided that the referenced value in A1 is either a
bonafide date or text (with no leading spaces or other complications). I
suspect that the post was an "exploratory question" and there may other
underlying issues or intentions.

***********
Regards,
Ron

XL2002, WinXP

(BTW...."obviate" is one of my favorite words)

> Isn't this whole question obviated by just entering the date in a cell and
> referencing that cell in the formula? =YEAR(A1) works fine if A1 contain
[quoted text clipped - 31 lines]
> > >
> > > > I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

Duke Carey - 07 Mar 2007 17:36 GMT

TRY

=YEAR("3/7/2007")

You are getting the year of the date represented by: 3 divided by 7 divided
by 2007, or a very small decimal amount.

> I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message

driller - 07 Mar 2007 23:36 GMT

Hi LadyCat,

It's quite obvious for me that you are trying to test the ability of the
YEAR() function...since you already type the Year "2007" inside the Year
function...

if you type a formula =year(A1) , where A1 contains a date, then you indeed
need to use a year function for some sort of YEAR series presentation...

or by another way is...

if its for presentation, then on A1, you can just format it "YYY" on the
format>cells>number>custom and type "yyy" [without the quotes]. In this way
you will not loose your real date value..

regards,
driller

Signature

*****
birds of the same feather flock together..

> I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

Reply to this Message