@EOMONTH glitch in QP8

There's a glitch in the @EOMONTH function for any dates AFTER 28-Feb-2100. 
(That shouldn't affect most (i.e., near-present context) applications, so this 
just FYI.)

For example,

   @EOMONTH(@DATE(2100,2,15),1)

which should return 31-Mar-2100 (the last day of the next month following 
15-Feb-2100), returns 1-Apr-2100 instead.  Any end-of-month dates later than 
Feb-2100 are likewise pushed one day too far by this function.

Similarly,

   @EOMONTH(@DATE(2100,2,15),-1)

which should return 31-Jan-2100 (the last day of the previous month, counting 
from 15-Feb-2100), returns 1-Feb-2100 instead.  Again, any end-of-month dates 
later than Feb-2100 involved in the function's calculation are off by one day.

This anomaly led me to take a little closer look at the history of calendars. 
Thus, the @EOMONTH glitch seems to be the result of QP8 not properly taking an 
obscure leap-year rule of the Gregorian calendar into account.  By definition 
(of the Julian calendar, and by extension, of the Gregorian calendar as well), 
all leap years are divisible by 4.  A key improvement of the Gregorian 
(present-day Western) calendar over the Julian (Roman) calendar was a provision

to skip certain leap years in order to keep the calendar in synchrony with the 
seasons.  Simply stated, the Julian calendar has too many leap years (when 
counting time over long intervals).  The Gregorian calendar keeps things in 
check by skipping 3 (regularly scheduled) leap years every 400 years.  The 
skipped leap years (i.e., those years that normally would have 29 days in 
February, but will have 28 days instead) are defined as years divisible by 100,

but NOT by 400.  In plain language, this means that years which would normally 
be leap years are NOT leap years if they are century years (i.e., 1700, 1800, 
etc.), EXCEPT for those century years divisible by 400 (i.e., 1600, 2000, etc.).

  Perhaps one reason why this rule is not so well recognized today is because 
2000 was one of those exceptional century years, i.e., one which normally would

have been a skipped leap year but wasn't because it is part of the rare 400-year

sequence.

For those among you with a time schedule too hurried for such esoteric nuances,

take comfort in the realization that this remains a non-issue until after 2099 
.... which should also provide plenty of lead-time for Corel to fix the 
function's algorithm before it becomes a critical issue.

Curiously, @AMNTHS does NOT have that date calculation problem, apparently 
knowing the calendar rules better than @EOMONTH, which (oddly) suggests that the

@AMNTHS coder was someone other than the @EOMONTH coder ... or it was the same 
guy (or gal) having a good day at work, and then a not so good day -- I guess I

can relate :-)

Cheers ... and happy calendaring,
Uli

lemoto wrote:
> Please: does the EndOfMonth option
> [# in @AMNTHS(@DATE(2100,2,1),1,#)]
> work for you in v8?
> 
> I found it ignored in v9ff, and now I find it ignored in 
> 8.0.0.709 also.
> I used: @AMNTHS(@DATE(2100,2,15),1,1)
> and QP reported 15th March.

Actually, @AMNTHS behaves just as it should.  The EndMonth option in @AMNTHS has

effect only under one condition:  the reference date MUST be the last day of the

month, in order for the calculated date to also fall on the last day of the new

month.  If the reference date is anything OTHER than an end-of-month date, the 
resulting day of the new month will exactly correspond to the day of the 
reference month, i.e., the 15th of one month will produce the 15th of another 
month, regardless of whether you activate (1) or deactivate (0) the EndMonth 
option.  Thus, whereas @EOMONTH always produces the last day of the month (until

the algorithm breaks down in Feb-2100), @AMNTHS yields an end-of-month date only

if the reference date is the last day of a month to begin with.

For example, changing the reference date to something where both functions are 
synchronized in their output, one can verify that

   @EOMONTH(@DATE(2099,2,28),1)  and
    @AMNTHS(@DATE(2099,2,28),1,1)

agree in the resulting date (as they should).  But moving the reference date 
over the critical threshold, you will notice that

   @EOMONTH(@DATE(2100,2,28),1)

has taken one step too many, while

    @AMNTHS(@DATE(2100,2,28),1,1)

has aptly kept the pace.  (This is a better example of what I meant by @AMNTHS 
*not* having the type of problem @EOMONTH had with the Feb-2100 threshold.)

Also, upon further investigation, I now see the purpose of the EndMonth option 
in @AMNTHS more clearly; thought must be given to exactly what kind of result 
you wish to obtain.  If you are moving from one month to another and want to 
arrive at the EXACT SAME DAY in each month -- and IF that particular day of the

month falls on either the 28th , 29th, or 30th -- then the only way of ensuring

that you obtain that day in any month of the year is to use 0 as the EndMonth 
parameter.  Using 1 as the EndMonth parameter for any of those 3 days will at 
some point in your time series produce true end-of-month dates, thereby breaking

up the desired pattern.

For example,

   A1:  28-Dec-2006
   A2:  @AMNTHS(A1,1,0) =  28-Jan-2007
   A3:  @AMNTHS(A2,1,0) =  28-Feb-2007
   A4:  @AMNTHS(A3,1,0) =  28-Mar-2007
   A5:  @AMNTHS(A4,1,0) =  28-Apr-2007

On the other hand,

   A2:  @AMNTHS(A1,1,1) =  28-Jan-2007
   A3:  @AMNTHS(A2,1,1) =  28-Feb-2007
   A4:  @AMNTHS(A3,1,1) =  31-Mar-2007
   A5:  @AMNTHS(A4,1,1) =  30-Apr-2007

In short, the activated EndMonth parameter (1) is only for hopping from one 
end-of-month date to another.

Cheers,
Uli

Could this be a leap year problem?
-- 
Jim Latham

Uli:

Well spotted!

Please: does the EndOfMonth option
[# in @AMNTHS(@DATE(2100,2,1),1,#)]
work for you in v8?

I found it ignored in v9ff, and now I find it ignored in 
8.0.0.709 also.
I used: @AMNTHS(@DATE(2100,2,15),1,1)
and QP reported 15th March.
-- 
Good wishes!
Roy Lewis
C_Tech volunteer
(UK)

Jim Latham wrote:
> Could this be a leap year problem?

Apparently so.  The year 2100 would normally be a leap year (i.e., divisible by

4), but by the Gregorian calendar rule any (normally scheduled) leap year which

is divisible by 100 but not by 400 is *NOT* a leap year.  Thus, the year 2100 is

one of those 3 rare "leap" years over a 400-year span whose leap-days
(29th Feb) 
are dropped.

Cheers,
Uli