Excel #Value error in Asia but not in the US

Davisro
Davisro used Ask the Experts™
on
I have a workbook managed in our Asia office that produces a #Value error in many columns but when they send the workbook to me in the US, I can't reproduce the problem. All of the columns in question have formulas that reference other columns on the same sheet.

My understanding is that #Value errors happen when you have a arithmetical formula referencing cells that are formatted as text, but i dont see that happening here...is there another reason this could happen?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Najam Uddin
Commented:
can you please share formula causing this? Is there any date related formula?
DavisroBudget Analyst

Author

Commented:
the sheet is complex, but yes, there are many date columns being evaluated as such as, for example, in cell A1 the following formula:

Cell A1
=if([today()] > B1, then return column C, elseif ([today()] > B2, then return column D, etc

Where: B1 = 07/01/2015 (formatted as date)
And B2 = 08/01/2015 (formatted as date)
etc for 12 months
NerdsOfTechTechnology Scientist

Commented:
Make sure all of the references columns are formatted STRICTLY as numbers or date (they must match TYPE across the board within the formula).

Also the regional settings are going to be much different.

See details about changing to date format to accommodate the traversal of date data.
http://excelsemipro.com/2011/06/regional-date-formats-in-excel/
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Start Today!
DavisroBudget Analyst

Author

Commented:
Can a general format cause it?
Technology Scientist
Commented:
In a nutshell, FORCE the DATE type on the date columns and use the SHORT DATE FORMAT (the format that begins with an asterisk) to allow the worldwide compatibility of the date data
DavisroBudget Analyst

Author

Commented:
Yep. The date formulas need to be formatted with the globally compatible format (starts with Asterisk). Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial