Deltadays and date functions.

[Vtile]

I have been in seach of simple algorithm to calculate dDays between two dates.

The JDN method is too heavy to my use (DM15/HP15C) as it needs to be accurate only between about 100 year time window.

I did found one that suits my use at: https://groups.google.com/forum/#!topic/...c9rcvY7d2s

Now interesting enough I did put 3 different methods to the excel worksheet to compare them simultaneusly.. If I take DDays from 28.2.1900 and 28.2.2100 I get results:

Excel (365): 73050
JDN-JDN^^: 73049
HP50g DDAYS: 73049
SIMPLE DDAYS**: 73050
WAlpha: 73049 ( https://www.wolframalpha.com/input/?i=DA...+28.2.2100 ) PS. As late though

So which ones are correct? :/ Any idea.. I trush 50g more than excel, since also the JDN-JDN gives the same result but..
First I though that excel handles days as Ddays+1, but no it were false assumption since

DDAYS(28.3.1900,28.2.2100) returns as follows:
Excel: 73021
JDN-JDN: 73021
HP50g DDAYS: 73021
Simple DDAYS: 73020

EDIT2: Ahaa, Excel DAYS function starts at year 1900 that might explain this difference in this particular case.

Interesting.. Back to jellyfying my brains with day algorithms. :Z


** From mailinglist discussion
Y <- start year
M <- start month
D <- start day
Y <- Y-1
M <- M+13
X <- int(365.25*Y)+int(30.6*M)+D
https://groups.google.com/forum/#!topic/...c9rcvY7d2s

^^
As given in wikipedia.org
https://en.wikipedia.org/wiki/Julian_day
https://wikimedia.org/api/rest_v1/media/...0958a53d61

[Don Shepherd]

here is the one I have been using for many years, on many different platforms, and it is easy to implement and works well:
dbd algorithm

I do the first one (calculate day number from date) for both dates and subtract.

[Vtile]

(05-10-2017 01:33 PM)Don Shepherd Wrote:  here is the one I have been using for many years, on many different platforms, and it is easy to implement and works well:
dbd algorithm

I do the first one (calculate day number from date) for both dates and subtract.

Unfortunately that is also as heavy (resources) as the JDN method. Heavy if the use case is only max.50 years in current century. Those gregorian leapyear corrections are unnecessary memory usage if one stays between 2000 and 2100 (what my current understanding is.).

Anyhow, someone else might have use of it.

[Don Shepherd]

(05-10-2017 01:28 PM)Vtile Wrote:  So which ones are correct?

The 12c, 17b, 17bii, and 65 (using the algorithm I mentioned) all return 73049 for dbd(2/28/1900,2/28/2100).

[Vtile]

(05-10-2017 02:42 PM)Don Shepherd Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  So which ones are correct?

The 12c, 17b, 17bii, and 65 (using the algorithm I mentioned) all return 73049 for dbd(2/28/1900,2/28/2100).

Thank you for the check, I think it is excels date format related thing, suprising that it starts from 1900 and not even from 1800s.

I start to think that I will adapt that same algorithm also (or just go with http://www.hpmuseum.org/forum/thread-6741.html). It is suprisingly annoying task to get everything sorted out and functioning with a few lines of code even with small time window.

[toml_12953]

(05-10-2017 01:28 PM)Vtile Wrote:  I have been in seach of simple algorithm to calculate dDays between two dates.

The JDN method is too heavy to my use (DM15/HP15C) as it needs to be accurate only between about 100 year time window.

I did found one that suits my use at: https://groups.google.com/forum/#!topic/...c9rcvY7d2s

Now interesting enough I did put 3 different methods to the excel worksheet to compare them simultaneusly.. If I take DDays from 28.2.1900 and 28.2.2100 I get results:

Excel (365): 73050
JDN-JDN^^: 73049
HP50g DDAYS: 73049
SIMPLE DDAYS**: 73050
WAlpha: 73049 ( https://www.wolframalpha.com/input/?i=DA...+28.2.2100 ) PS. As late though

So which ones are correct? :/ Any idea.. I trush 50g more than excel, since also the JDN-JDN gives the same result but..
First I though that excel handles days as Ddays+1, but no it were false assumption since

DDAYS(28.3.1900,28.2.2100) returns as follows:
Excel: 73021
JDN-JDN: 73021
HP50g DDAYS: 73021
Simple DDAYS: 73020

EDIT2: Ahaa, Excel DAYS function starts at year 1900 that might explain this difference in this particular case.

Interesting.. Back to jellyfying my brains with day algorithms. :Z


** From mailinglist discussion
Y <- start year
M <- start month
D <- start day
Y <- Y-1
M <- M+13
X <- int(365.25*Y)+int(30.6*M)+D
https://groups.google.com/forum/#!topic/...c9rcvY7d2s

^^
As given in wikipedia.org
https://en.wikipedia.org/wiki/Julian_day
https://wikimedia.org/api/rest_v1/media/...0958a53d61

One might include both endpoints and one might not.
The difference between 1/5/2017 and 2/5/2017 would be 1 or 2 depending on that.

Tom L

Tom L

[KeithB]

They also might not be handling the millennial leap year correctly. I don't even remember what it was supposed to be. 8^)

[Dieter]

(05-10-2017 01:28 PM)Vtile Wrote:  I have been in seach of simple algorithm to calculate dDays between two dates.
The JDN method is too heavy to my use (DM15/HP15C)

What? Heavy? A program that works correctly for any valid Gregorian date can be done in less than 80 steps. Take a look at the old HP41 library on this website.

(05-10-2017 01:28 PM)Vtile Wrote:  ...as it needs to be accurate only between about 100 year time window.

In this case things can be simplified a bit. For dates between (and including) 1 Mar 1900 and 28 Feb 2100 every fourth year is a leap year (that's because 2000 is one). This allows to use a simpler formula.

(05-10-2017 01:28 PM)Vtile Wrote:  I did found one that suits my use at: https://groups.google.com/forum/#!topic/...c9rcvY7d2s

Hmmm... looks like a few things have been mixed up there. See below.

(05-10-2017 01:28 PM)Vtile Wrote:  Now interesting enough I did put 3 different methods to the excel worksheet to compare them simultaneusly.. If I take DDays from 28.2.1900 and 28.2.2100 I get results:

Excel (365): 73050
JDN-JDN^^: 73049
HP50g DDAYS: 73049
SIMPLE DDAYS**: 73050
WAlpha: 73049 ( https://www.wolframalpha.com/input/?i=DA...+28.2.2100 ) PS. As late though

So which ones are correct?

73049 is correct. As usual, Excel cannot be trusted under all circumstances. The date functions only work for a certain range, so you better write your own.

(05-10-2017 01:28 PM)Vtile Wrote:  ** From mailinglist discussion
Y <- start year
M <- start month
D <- start day
Y <- Y-1
M <- M+13
X <- int(365.25*Y)+int(30.6*M)+D

As already mentioned, some things get mixed up here. For instance an Y and M adjustment has to be applied only for Jan and Feb, and not for all months. For dates between 1 Mar 1900 and 28 Feb 2100 you may use the following method:

Code:

Count Jan and Feb as month 13 and 14 of the previous year:
IF M<3 THEN M=M+12 and Y=Y-1

Then calculate N = INT(Y*365,25) + INT((M+1)*30,6) + D

Δdays = N(date2) - N(date1)

A 15C program can be done in a few minutes:

Code:

001 LBL D
002 STO 0
003 R↓
004 3
005 x<=y?
006 GTO 00
007 +
008 9
009 +
010 1
011 STO- 0
012 LBL 00
013 R↓
014 1
015 +
016 3
017 0
018 ,
019 6
020 x
021 INT
022 +
023 RCL 0
024 3
025 6
026 5
027 ,
028 2
029 5
030 x
031 INT
032 +
033 RTN

This way day [ENTER] month [ENTER] year f[D] returns N(date).
Since the uppermost stack register is preserved, the number of days between two dates can be determined easily:

28 [ENTER] 2 [ENTER] 2100 f[D] => 767146
  1 [ENTER] 3 [ENTER] 1900 f[D] => 694098
  [–] => 73048

Dieter

Edit: changed program slightly (omitted DSE 0 and RCLx 0) so that it can also be used with most traditional RPN HPs.

[Vtile]

Heh. It definedly is easy when one knows what he is doing. Superb post Dieter, many thanks. I'll take a closer look at it tomorrow, as I have already spend too much time today reading about the calendar system(s).

[Dieter]

(05-10-2017 05:12 PM)Vtile Wrote:  Heh. It definedly is easy when one knows what he is doing. Superb post Dieter, many thanks. I'll take a closer look at it tomorrow, as I have already spend too much time today reading about the calendar system(s).

Thank you very much. With just a few additional steps the program should even work for any valid Gregorian date (i.e. since 15 Oct 1582) that does not cause roundoff errors when Y is multiplied by 365,25 – but well, I did not test any year. ;-)

Code:

... ...
031 INT
032 +
033 RCL 0
034 EEX
035 2
036 /
037 INT
038 -
039 LstX
040 4
041 /
042 INT
043 +
044 RTN

Example:
31 [ENTER] 12 [ENTER] 9999  f [D] => 3652487
15 [ENTER] 10 [ENTER] 1582  f [D] =>  578164
[–] => 3074323 days

Dieter

Edit: changed first steps of the code to reflect edited version of original program.

[Vtile]

Naturally it started to rain (a snow!) again when I got outside.. So I get in to this today.

(05-10-2017 05:01 PM)Dieter Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  I have been in seach of simple algorithm to calculate dDays between two dates.
The JDN method is too heavy to my use (DM15/HP15C)

What? Heavy? A program that works correctly for any valid Gregorian date can be done in less than 80 steps. Take a look at the old HP41 library on this website.

No no no... Not a heavy as is, but judged by the use case. Ie. as a part of financial calculation programs. Or just to check how far the next holiday were.

In general JDN algorithm is really sophisticated and simple (that sounds like I would understand it through).

(05-10-2017 05:01 PM)Dieter Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  ...as it needs to be accurate only between about 100 year time window.

In this case things can be simplified a bit. For dates between (and including) 1 Mar 1900 and 28 Feb 2100 every fourth year is a leap year (that's because 2000 is one). This allows to use a simpler formula.

But isn't 2100 a regular year as it is divisable with 100 and 4, but not 400. I must be mixing up something now. (Assuming the RPN program you kindly provided doesn't take somehow account the "regular year if divisable with 100" rule. My RPN still needs to some practice to fluently read it.)

(05-10-2017 05:01 PM)Dieter Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  I did found one that suits my use at: https://groups.google.com/forum/#!topic/...c9rcvY7d2s

Hmmm... looks like a few things have been mixed up there. See below.

I see it now, while I still fully understand it.

(05-10-2017 05:01 PM)Dieter Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  Now interesting enough I did put 3 different methods to the excel worksheet to compare them simultaneusly.. If I take DDays from 28.2.1900 and 28.2.2100 I get results:

Excel (365): 73050
JDN-JDN^^: 73049
HP50g DDAYS: 73049
SIMPLE DDAYS**: 73050
WAlpha: 73049 ( https://www.wolframalpha.com/input/?i=DA...+28.2.2100 ) PS. As late though

So which ones are correct?

73049 is correct. As usual, Excel cannot be trusted under all circumstances. The date functions only work for a certain range, so you better write your own.

This were suprising, while I did know that it do have quirks in number handling etc. (which I learned from here) I would have assumed it have state of the art calendar functions for accounting and so forth reasons.

(05-10-2017 05:01 PM)Dieter Wrote:  

(05-10-2017 01:28 PM)Vtile Wrote:  ** From mailinglist discussion
Y <- start year
M <- start month
D <- start day
Y <- Y-1
M <- M+13
X <- int(365.25*Y)+int(30.6*M)+D

As already mentioned, some things get mixed up here. For instance an Y and M adjustment has to be applied only for Jan and Feb, and not for all months. For dates between 1 Mar 1900 and 28 Feb 2100 you may use the following method:

Code:

Count Jan and Feb as month 13 and 14 of the previous year:
IF M<3 THEN M=M+12 and Y=Y-1

Then calculate N = INT(Y*365,25) + INT((M+1)*30,6) + D

Δdays = N(date2) - N(date1)

Yes, now I see the function of the 0,25 (1/4) as it is calculated to days with INT/FLOOR only each four years, so it makes the leap year handling in this narrow implementation.

On the other hand I still not get it how the months are handled so sophisticated that the sequence 31,28(**),31,30,31,30,31,31,30,31,30,31 (2+5+5) drops in to that 0.6 with the month shifting IF M<3 THEN M=M+12 and Y=Y-1.. *scratching his head*

**not +1 since that is handled by years multiplier with fraction .25

Well it is 3/5 so it is also found from the JDN algorithm and also from the algorithm mentioned by Don Shepherd.. Somehow it relates to that divisor 5. Well someday I need try to figure how the gears rotate on that one.

(05-10-2017 05:01 PM)Dieter Wrote:  A 15C program can be done in a few minutes:

Code:

001 LBL D
002 STO 0
003 R↓
004 3
005 x<=y?
006 GTO 00
007 +
008 9
009 +
010 ENTER
011 DSE 0
012 LBL 00
013 R↓
014 1
015 +
016 3
017 0
018 ,
019 6
020 x
021 INT
022 3
023 6
024 5
025 ,
026 2
027 5
028 RCLx 00
029 INT
030 +
031 +
032 RTN

This way day [ENTER] month [ENTER] year f[D] returns N(date).
Since the uppermost stack register is preserved, the number of days between two dates can be determined easily:

28 [ENTER] 2 [ENTER] 2100 f[D] => 767146
  1 [ENTER] 3 [ENTER] 1900 f[D] => 694098
  [–] => 73048

Dieter

Do you mind if put a link to this program in my (X)IRR/(X)NPV topic at http://www.hpmuseum.org/forum/thread-8322.html.

Edit. I lost what I were going to edit.. curse of long quote of quote posts.

[Vtile]

(05-10-2017 03:17 PM)toml_12953 Wrote:  One might include both endpoints and one might not.
The difference between 1/5/2017 and 2/5/2017 would be 1 or 2 depending on that.

Tom L

You are correct, but I think miscalculation on the excel did relate how it handles the year 1900 as its day function seems to start at 1.1.1900 (IIRC). On the otherhand I also foolishly didn't think and assumed that days between dates would return 0 if dates are following each other (even though I implemented ie. the JDN dDays correctly), but there is still one day (24 hours) round loop between *oops* start and finish line, while there is zero dates between. My gibberish visualisation for it now, hopefully I remember.

[Dieter]

(05-10-2017 07:16 PM)Vtile Wrote:  But isn't 2100 a regular year as it is divisable with 100 and 4, but not 400. I must be mixing up something now.

Leap years are those that can be divided by 4, but not by 100, and then also those that are divisible by 400. So no, 2100 is not a leap year. That's why the simple program version works until 28 Feb 2100 but not beyond. Remember, regular and leap years only differ after Feb 28 where the next day may be Feb 29 or 1 Mar. ;-)

(05-10-2017 07:16 PM)Vtile Wrote:  (Assuming the RPN program you kindly provided doesn't take somehow account the "regular year if divisable with 100" rule. My RPN still needs to some practice to fluently read it.)

The first, simplified program version does not care about the divisible-by-100 rule at all. It simply assumes that every 4th year is a leap year. Which is true for the given date range as it starts not before Mar 1900 and it ends in Feb 2100. The special case 2000 is divisible by 400 and so it is a leap year, like any other in the given date range that is divisible by 4. That's why the simplified formula works for a 200-year-interval instead of just a century.

(05-10-2017 07:16 PM)Vtile Wrote:  This were suprising, while I did know that it do have quirks in number handling etc. (which I learned from here) I would have assumed it have state of the art calendar functions for accounting and so forth reasons.

You should see what Excel does with some higher-level math functions, or with statistical distributions... #-)

(05-10-2017 07:16 PM)Vtile Wrote:  Yes, now I see the function of the 0,25 (1/4) as it is calculated to days with INT/FLOOR only each four years, so it makes the leap year handling in this narrow implementation.

Yes.

(05-10-2017 07:16 PM)Vtile Wrote:  On the other hand I still not get it how the months are handled so sophisticated that the sequence 31,28(**),31,30,31,30,31,31,30,31,30,31 (2+5+5) drops in to that 0.6 with the month shifting IF M<3 THEN M=M+12 and Y=Y-1.. *scratching his head*

That's not too hard to understand. Remember that a year starts with March (M=3) and ends with next year's February (M=14). So leap year or not is not a question here: the first day is 1 March, and at the end of the year at most 28 or 29 days are added.

Now write down INT(30,6*(M+1)) and compare this with the number of days accumulated in a year. The INT expression is just the number of elapsed days since 1 March, plus 122. The latter does not matter here. Subtract 122, and for M=14 (Feb) you get 337. Now add 28 days and get 365 days of a regular year.

So the 30,6 is simply a factor that generates the sequence 0, 31, 61, 92... days that elapsed since 1 March.

(05-10-2017 07:16 PM)Vtile Wrote:  Well it is 3/5 so it is also found from the JDN algorithm and also from the algorithm mentioned by Don Shepherd.. Somehow it relates to that divisor 5.

Instead of multiplying by 30,6 you can also multiply by 153/5 which is the preferred solution for most computer languages that use binary number encoding which may result in roundoff errors here: the result of 30,6x5 may be 152,9999... instead of 153 so that INT is off by one. That's why you often find 30,6001 or 30,61 instead of plain 30,6. For BCD calculators like ours this is not required. In most computer programming languages the preferred solution is evaluating 153*(m+1) which is an integer, and then do an integer division (DIV) by 5. The same idea applies to 365,25*Y resp. (1461*Y) DIV 4.

(05-10-2017 07:16 PM)Vtile Wrote:  Do you mind if put a link to this program in my (X)IRR/(X)NPV topic at http://www.hpmuseum.org/forum/thread-8322.html.

No problem. But I think you should link to the second improved version, the one without the limitations of the original simplified one. If you want a more sophisticated calendar program you may want to take a look at this one.

And finally, here is another one that uses a different approach for date differences. ;-)

Dieter

[Dieter]

(05-10-2017 07:55 PM)Vtile Wrote:  You are correct, but I think miscalculation on the excel did relate how it handles the year 1900 as its day function seems to start at 1.1.1900 (IIRC).

I am not sure if this has been changed in recent versions of Excel, but actually the date calculations are incorrect for dates before 1 March 1900. Although 1900 was not a leap year, you may enter 29 Feb 1900 without an error message, and the difference between 1 March 1900 and 28 Feb 1900 is miscalculated as 2 days instead of one.

As with several other functions, the golden rule is: "code your own". VBA exists, and it allows doing things right that the built-in functions don't.

Dieter

[Vtile]

Quickly to excel ... Atleast this new Office365 version I have at my disposal does give #value-error for dates before 1.1.1900 when trying to apply ie. Dates, or DATEVALUE functions (non-VBA functions) to it. I need to try to locate my herited Office95 Compact Disc and try it (to fill my own curiosity).

[AndiGer]

Dieter didn't say dates before January 1 1900 but before March 1 1900.
Andi

[Vtile]

(05-11-2017 04:12 PM)AndiGer Wrote:  Dieter didn't say dates before January 1 1900 but before March 1 1900.
Andi

You are correct, I did read it too hastily.

[Dieter]

(05-11-2017 11:33 AM)Vtile Wrote:  Quickly to excel ... Atleast this new Office365 version I have at my disposal does give #value-error for dates before 1.1.1900 when trying to apply ie. Dates, or DATEVALUE functions (non-VBA functions) to it.

Sure, the earliest allowed date is 1 Jan 1900. But the first correctly handled date is 1 March 1900. #-)

BTW, here is what Microsoft says on this issue. Read it and have a good laugh.

FTR: the mentioned programs Lotus 1-2-3 and MS Multiplan appeared in the early Eighties, about the same time as the Commodore 64 and the HP-41CX. #-)

Dieter

[toml_12953]

(05-11-2017 05:03 PM)Dieter Wrote:  

(05-11-2017 11:33 AM)Vtile Wrote:  Quickly to excel ... Atleast this new Office365 version I have at my disposal does give #value-error for dates before 1.1.1900 when trying to apply ie. Dates, or DATEVALUE functions (non-VBA functions) to it.

Sure, the earliest allowed date is 1 Jan 1900. But the first correctly handled date is 1 March 1900. #-)

BTW, here is what Microsoft says on this issue. Read it and have a good laugh.

FTR: the mentioned programs Lotus 1-2-3 and MS Multiplan appeared in the early Eighties, about the same time as the Commodore 64 and the HP-41CX. #-)

Dieter

I'm surprised MS didn't lobby to have 1900 officially changed to be a leap year!

Tom L

[PANAMATIK]

The HP-01 calculates the correct result 73049. But as the highest year, which can be entered, is 2099, I calculated 28/2/99.-28/2/00+365=73049

Bernhard