i got #value* while extract year from excel d

March 11, 2011 at 01:25:42
Specs: Windows XP
anyone can help me on this,

I have a report in the following format, and i would like to extract the year, both line has a same cell format but when i tried to extract year using command i.e. =year(O7) and =year(O8) and one of them having result "#value*) the other one appear correctly:-

cell O7 Date : 25.3.2012 I got correct year 2012
Cell O8 Date : 29.2.2010 for this i got "#value"

Would appreciate if anyone can help me on this.

Thank you.

See More: i got #value* while extract year from excel d

Report •

March 11, 2011 at 12:19:21
Make sure both cells are indeed DATE cells,

Format both cells as Numbers,
you should see a five digit number
if you don't, then the cell is formatted as TEXT



Report •

March 15, 2011 at 05:01:21
I am very surprised that you get an answer of 2012 for '=year(O7) '.
25.3.2012 is not considered a Date.
Dates can use "/" or "-" as separators, but not "."
Excel treats any Date with "." as text.

The only way Excel sees it as a Date, is because it is a Date, but your Cell Format make it look like 25.3.2012
(Custom Format: "dd.m.yyyy")

Now, for "29.2.2010"...
You will get an error there.
There is no 29th of February in 2010.
The only way that will give you a Year is if the year is 2012.
The previous year that worked, was 2008.

Report •
Related Solutions

Ask Question