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.
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.
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.
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.
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..
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.
