Forum Discussion
Excel 2013 compatibility with Excel 2016
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"
