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

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

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

Enclose the date quotes:

=YEAR("3/7/2007")

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

--
HTH

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


with @tiscali.co.uk


"LadyCat" wrote in message
...
I am expecting the answer as 2007 from the function of =YEAR(3/7/2007)

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

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

Ladycat,

Put the date in quotes?

Beege

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

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


"LadyCat" wrote:

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

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

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.


"LadyCat" wrote:

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

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

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


"Ron Coderre" wrote:

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


"LadyCat" wrote:

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

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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Ron Coderre" wrote:

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


"Ron Coderre" wrote:

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


"LadyCat" wrote:

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

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

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)

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Ron Coderre" wrote:

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


"Ron Coderre" wrote:

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


"LadyCat" wrote:

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

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

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
formatcellsnumbercustom and type "yyy" [without the quotes]. In this way
you will not loose your real date value..

regards,
driller

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



"LadyCat" wrote:

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