Month calculations: DATE() vs EDATE()

Question

Level 6

17,794 points

Month calculations: DATE() vs EDATE()

In a recent topic ( Challenge to get a date correctly), I mentioned using the DATE function to calculate a date that is (for example) one month later than a given one. Specifically, if cell A1 contains the given date, then I suggested using this formula for a date one month later:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

Yvan Koenig suggested instead using the simpler EDATE formula, here equivalent to:

EDATE(A1,1)

There is, as it turns out, more than simplicity in favor of Yvan's approach. While one might expect the two formulas always to produce the same results, they do not! This is easily seen by constructing the following three column table with a column header:

1. In cell A2, enter the last day of the first month of this year (January 31, 2008 in the U.S. system, for example). In the cell below it, enter the last day of the second month of this year (Feburary 29, 2008, for example).

2. Next, select both cells & use the circular fill handle, drawing down to fill the column with a year or so of last-day-of month values.

3. Select cell B1 (a header cell) & enter in the formula bar & press return:

=DATE(YEAR(A),MONTH(A)+1,DAY(A))

4. Likewise, in cell C1 enter:

=EDATE(A,1)

Note that the B & C column values are sometimes the same, sometimes not. The same results occur if the "1" in both formulas is replaced with another number of months.

What seems to be happening is the 'MONTH(A)+n' expression uses the number of days in the month of the "A" cell value as the basis for the 'n months later' calculation, which is not the convention usually used for such things as billing cycles. The EDATE formula follows the normal convention, & is thus the preferred choice for almost all uses imaginable ... unless perhaps you are the one being billed. 🙂

This also applies to 'YEAR(A) + n' calculations.

So, it would seem that any calculation involving the DATE(year, month, day) form with an arithmetic operator in the year or month element should be used only with great care, if at all.

BTW, the comments about the month unit of calendar time having "no real purpose today" in http://www.cl.cam.ac.uk/~mgk25/iso-time.html may be of interest.

iMac G5/2.0 GHz 17" ALS; White MacBook/2.4 GHz, Mac OS X (10.5.4), Kensington Trackball

Posted on Jul 30, 2008 4:38 PM

Reply

Answer 1

KOENIG Yvan

Level 8

41,825 points

Jul 31, 2008 1:37 AM in response to R C-R

Thank's for these comments.

I file a bug report.
The long formula generates a blue triangle when we add 1 to december.
A cleaner formula would be:
=DATE(IF(MONTH(B) 1>12,YEAR(B)+1,YEAR(B)),IF(MONTH(B)+1>12,MONTH(B)+1-12,MONTH(B)1),DAY(B))

Same comment for the one adding some days.
I wrote a version getting rid of the num Day becoming higher than 31
=DATE(YEAR(B),IF((DAY(B) 14)>31,MONTH(B)+INT((DAY(B)-1+14)/31),MONTH(B)),IF((DAY(B)+14)>31,MOD(DAY(B)14,31),DAY(B)))
but it may generate the blue triangle if the addition forces to change of year.

Yvan KOENIG (from FRANCE jeudi 31 juillet 2008 10:19:02)

Reply

Answer 2

R C-R Author

Level 6

17,794 points

Jul 31, 2008 2:33 AM in response to KOENIG Yvan

I'm aware of the blue warning triangle appearing in these "overflow" situations; however, even when it does not, the results may be different -- for example, with a starting date of January 31st, adding one month in my long formula produces a month argument that is in bounds but (for 2008) yields a date 2 days later than with the EDATE method.

I don't view this so much a bug as a consequence of the vague nature of the "n months later" or of the "month offset" concept. As the cited scholarly article points out, the concept of the months of the year are of somewhat obscure mystic origins, & there lengths were arbitrarily set, often for reasons more political than practical. Between the 1st & 28th day of any month, the concept is unambiguous -- it is the same numbered day in the other month, but for the 29th through 31st day of the month it is not, depending on the starting month.

From what little I have been able to discover from casual research, the EDATE results are the standard more by custom than by any well-defined rule: what we mean by the offset references the first, last, or some near-the-end-of-the-month day number, whichever seems the most suitable at the time.

I do not have Excel on my Macs, but since the functions are similar, I would be interested in learning how that application behaves with this table.

Reply

Answer 3

KOENIG Yvan

Level 8

41,825 points

Jul 31, 2008 3:41 AM in response to R C-R

I used the formula "I file a bug report" because it's a report sent to bugs hunters 😉

The exact contents was:

+_There is at least an inconsistency_ in date calculations in Numbers.+

+If we use =EDATE(A,1) to add one month to the date stored in A+
+the result is not always the same than the one returned by:+
=DATE(YEAR(A),MONTH(A)+1,DAY(A))
+which is supposed to do the same.+

+The offset may reach 3 days.+

As you may see, I didn't decide if it is a bug or a surprising feature.

Yvan KOENIG (from FRANCE jeudi 31 juillet 2008 12:41:42)

Reply

Answer 4

R C-R Author

Level 6

17,794 points

Jul 31, 2008 4:33 AM in response to KOENIG Yvan

I wonder if an inconsistency that is common to both Numbers & Excel (if that is the case) is something that should be eliminated in one but not the other, or even in both, if it causes legacy issues.

This is a little like the concept of months itself -- it may not be the most logical way to organize days, but it is one so entrenched in most cultures that it would be unwise to try to change it.

Aside from that, I'm not entirely sure that the two formulas are supposed to give the same results, although I think the differences should be added to the documentation so that users can be made aware of them. I can't imagine why anyone would choose the DATE method knowing about the differences, but I have learned that it is never wise to assume that sort of thing about someone else's needs.

Reply