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

R

Ron Coderre

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
 
D

Duke Carey

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.
 
R

Ron Coderre

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
 
D

Dave F

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
 
R

Ron Coderre

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)
 
D

driller

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
 
Top