Excel Formula Returning #VALUE "=RIGHT(1952,1)"

"=RIGHT(1952,1)"

Works in a new workbook...but in my existing workbook it displays #VALUE...

Not sure what could be causing this.  Some setting?

Thanks.
TonicacentAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor Commented:
In File...Options...Advanced menu item, go to the very bottom of the right pane and uncheck the option for Transition formula evaluation
0
 
KCTSCommented:
RIGHT is use to take the rightmost part of a string, since 1952 is a number you are getting this error.

=RIGHT("1952",1) would work and return a "2" in this case - is that what you want ?
0
 
TonicacentAuthor Commented:
Thanks that does work.  I was just confused because it works fine without quotations in other 97-2003 xls workbooks....anyone no more headache.  Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TonicacentAuthor Commented:
now I have a problem with this formula...

=RIGHT(YEAR(AB33),2)

AB33 = 04/10/1952

returns #VALUE

This is just so frustrating, because this works just fine in any other workbook....im going to have to copy everything over to a new workbook.
0
 
byundtCommented:
Your formula is working in my copy of Excel 2010, both when AB33 contains text that looks like a date and also when it contains a date formatted as mm/dd/yyyy

Is the calculation mode in your workbook set to Manual (see File...Options...Formulas menu item)?

Does your default date format put the day before the month?  dd/mm/yyyy

If you could post a workbook that illustrates the problem, it would be very helpful in trying to resolve the issue.
0
 
TonicacentAuthor Commented:
Here is the file im working in...removed all data except the part im struggling with.
NewComp-Template-test.xls
0
 
TonicacentAuthor Commented:
nice.  Thank you byundt
0
 
byundtCommented:
It's a good thing you were able to post a sample workbook--I'd have never figured it out otherwise.

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.