Excel 2013 compatibility with Excel 2016

[melissaw]

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?

[#2]

@melissaw Read more here about #VALUE! errors here:

https://support.office.com/en-us/article/How-to-correct-a-VALUE-error-15e1b616-fbf2-4147-9c0b-0a11a2... 

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.

[#3]

@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.

[#4]

@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"

[#5]

@LidiaMThanks for your reply. The sumifs formulas are linked to cell references. The dates are not hard keyed into the formulas.

[#6]

@melissaw And what about regional standards? Are they the same for 2013 and 2016 users?

[#7]

@LidiaMYes, regional standards are the same.

[#8]

@melissawCan you try DATE function? for example for 2019-31-12 in A1 use

DATE(LEFT(A1;4);RIGHT(A1;2);MID(A1;6;2)).

It should work in both cases.