Forum Discussion
Excel 2013 compatibility with Excel 2016
We have a mix of Excel 2013 and Excel 2016 users at our workplace. We are running into compatibility issues.
For example, an Excel file created in Excel 2013 has working formulas. When this file is opened by another user in Excel 2016, the formulas now return #value errors. i.e. these formulas do not appear to function in Excel 2016, but reopening the file in Excel 2013 resolves this issue.
Suggestions on how to resolve this?
7 Replies
melissaw Read more here about #VALUE! errors here:
Perhaps it leads you to a solution. If not, could you please give some examples of formulae that work in 2013 but not in 2016.
Riny_van_EekelenThanks for the reply. The problem doesn't appear to be with the formulas themselves as they function correctly in Excel 2013.
For a specific example, the file in Excel 2013 has formulas that are driven off of dates (e.g. sumifs formula that sum values based on certain date ranges). When opened in Excel 2016, Excel no longer recognizes the date values as dates (the sumifs return zero values). Excel also does not seem to recognize the date values as text either (putting in the formula =DATEVALUE to convert the text value to a date returns #value). When reopened in Excel, however, the =DATEVALUE formulas return real values (i.e. they no longer return #value).
From what I can see in this specific example, the values for the dates are unrecognizable by Excel 2016 as either dates or text.
melissawThe problem can be not in Excel versions compatibility, but in different regional standards for Windows. You can try to solve this problem by using cell references for dates instead of constants. This way a date will be in a right format for every user. For example, use in your sumifs formulas
">="&A1, there A1=2019-31-12
instead of
">=2019-31-12"
