EDATE function not working in formula since upgrade to Sierra

Question

EDATE function not working in formula since upgrade to Sierra

I have a formula which uses the EDATE function to reference a date in another cell. It worked fine on earlier OSX versions but since upgrade to Sierra the formula always returns zero. The formula is:

=D$4*SUMIFS('Insurance - Julian' :: $B,'Insurance - Julian' :: $A,">"&EDATE(D$3,-12),'Insurance - Julian' :: $A,"<="&D$3,'Insurance - Julian' :: $G,"="&$A21,'Insurance - Julian' :: $Category,"="&$A$3)

and cell D$3 contains a valid date.

Curiously the EDATE function works in isolation. I've tested this by entering =EDATE(D$3,-12) in another cell and it returns a date which is 12 months less than the date in D$3, which is as expected.

I've also replaced ">"&EDATE(D$3,-12) in the formula with ">"&A1 where A1 contains a datestring and this works which proves that the rest of the formula is OK. The problem clearly lies with the EDATE function not returning the expected datevalue when it is embedded in the formula. Very weird. Like I say, this has been working for years in earlier versions of OSX

Posted on Aug 3, 2017 2:50 PM

Reply

Answer 1

Aug 4, 2017 9:22 AM in response to SGIII

Hi. Here's a screenshot which might help. I'm using Numbers 2.3 (it's an old and very data rich sheet which would take forever to port to version 3 so please don't ask me to port - I just don't have the time).

I've focussed on cell D21 (although most cells on this sheet contain a similar formula).

I've also attached a screenshot of the Insurance sheet which is referenced within the formula so you can see the data on which the SUMIFS function is acting.

Reply

Answer 2

Aug 4, 2017 12:10 PM in response to SGIII

Thanks for your thoughts. I have unmerged all cells in the Insurance table as you suggest but to no avail.

I realise version 2 is unsupported but I tried porting when v3 came out (my file was first created 10 years ago!) and I'm afraid Apple did not do a good job of backward compatibility for large files such as mine. I have tried several times since but the port is not even close and the effort required to fix the literally thousands of corrupted cells would be more than if I were to port to Excel (which I'm loathe to do).

I have over 100 tables across 34 sheets (and this is after several rounds of rationalisation) some incredibly large. Also It's harder to navigate the one dimensional horizontal sheet tabs in v3/4 compared with the vertical (and nest-able) navigation of 2 and believe me this really makes a difference for large files with many sheets and tables so I prefer to stay with v2.

This bug was triggered by upgrade to Sierra which I now regret. I'm not sure how easy it is to go back to the earlier OSX - thoughts?

Just noticed that the screenshot showed a revised version of the problem formula, where I was troubleshooting. I've attached the original version which includes the problematic EDATE function.

Reply

Answer 3

SGIII

Level 8

37,550 points

Aug 4, 2017 9:57 AM in response to Can't get this to work

The screenshots make the situation a little clearer. Some thoughts:

Numbers 2 is "orphanware" now-- eight years old and Apple no longer supports it.

Numbers 4 (and 3) often can import old documents from Numbers 2; if you haven't tried this already, you'll know within a minute or two whether it will work.

You appear to have merged cells in the 'Insurance - Julian' table. Merged cells can cause formulas to yield unexpected results. You might try unmerging the cells there. (You can easily achieve the same visual effect without cells being merged).

SG

Reply

Answer 4

SGIII

Level 8

37,550 points

Aug 3, 2017 6:09 PM in response to Can't get this to work

It's hard to know what might not be working in the formula without seeing it in context and understanding better what you are trying to get it to do. Screenshots might help.

SG

Reply