Deltadays and date functions.

05102017, 01:28 PM
(This post was last modified: 05102017 02:14 PM by Vtile.)
Post: #1




Deltadays and date functions.
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 JDNJDN^^: 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 JDNJDN 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 JDNJDN: 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 < Y1 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 

05102017, 01:33 PM
(This post was last modified: 05102017 01:36 PM by Don Shepherd.)
Post: #2




RE: Deltadays and date functions.
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. 

05102017, 01:50 PM
(This post was last modified: 05102017 01:51 PM by Vtile.)
Post: #3




RE: Deltadays and date functions.
(05102017 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: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. 

05102017, 02:42 PM
Post: #4




RE: Deltadays and date functions.  
05102017, 03:02 PM
(This post was last modified: 05102017 03:04 PM by Vtile.)
Post: #5




RE: Deltadays and date functions.
(05102017 02:42 PM)Don Shepherd Wrote: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.(05102017 01:28 PM)Vtile Wrote: So which ones are correct? I start to think that I will adapt that same algorithm also (or just go with http://www.hpmuseum.org/forum/thread6741.html). It is suprisingly annoying task to get everything sorted out and functioning with a few lines of code even with small time window. 

05102017, 03:17 PM
Post: #6




RE: Deltadays and date functions.
(05102017 01:28 PM)Vtile Wrote: I have been in seach of simple algorithm to calculate dDays between two dates. 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 Tom L Cui bono? 

05102017, 04:30 PM
Post: #7




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


05102017, 05:01 PM
(This post was last modified: 05102017 09:17 PM by Dieter.)
Post: #8




RE: Deltadays and date functions.
(05102017 01:28 PM)Vtile Wrote: I have been in seach of simple algorithm to calculate dDays between two dates. 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. (05102017 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. (05102017 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. (05102017 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: 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. (05102017 01:28 PM)Vtile Wrote: ** From mailinglist discussion 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: A 15C program can be done in a few minutes: Code: 001 LBL D 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. 

05102017, 05:12 PM
Post: #9




RE: Deltadays and date functions.
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).


05102017, 06:17 PM
(This post was last modified: 05102017 09:13 PM by Dieter.)
Post: #10




RE: Deltadays and date functions.
(05102017 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: ... ... 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. 

05102017, 07:16 PM
(This post was last modified: 05102017 07:20 PM by Vtile.)
Post: #11




RE: Deltadays and date functions.
Naturally it started to rain (a snow!) again when I got outside.. So I get in to this today.
(05102017 05:01 PM)Dieter Wrote: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.(05102017 01:28 PM)Vtile Wrote: I have been in seach of simple algorithm to calculate dDays between two dates. In general JDN algorithm is really sophisticated and simple (that sounds like I would understand it through). (05102017 05:01 PM)Dieter 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. (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.)(05102017 01:28 PM)Vtile Wrote: ...as it needs to be accurate only between about 100 year time window. (05102017 05:01 PM)Dieter Wrote:I see it now, while I still fully understand it.(05102017 01:28 PM)Vtile Wrote: I did found one that suits my use at: https://groups.google.com/forum/#!topic/...c9rcvY7d2s (05102017 05:01 PM)Dieter 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.(05102017 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: (05102017 05:01 PM)Dieter 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.(05102017 01:28 PM)Vtile Wrote: ** From mailinglist discussion 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=Y1.. *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. (05102017 05:01 PM)Dieter Wrote: A 15C program can be done in a few minutes:Do you mind if put a link to this program in my (X)IRR/(X)NPV topic at http://www.hpmuseum.org/forum/thread8322.html. Edit. I lost what I were going to edit.. curse of long quote of quote posts. 

05102017, 07:55 PM
Post: #12




RE: Deltadays and date functions.
(05102017 03:17 PM)toml_12953 Wrote: One might include both endpoints and one might not.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. 

05102017, 08:17 PM
Post: #13




RE: Deltadays and date functions.
(05102017 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. ;) (05102017 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 divisibleby100 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 200yearinterval instead of just a century. (05102017 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 higherlevel math functions, or with statistical distributions... #) (05102017 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. (05102017 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=Y1.. *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. (05102017 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. (05102017 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/thread8322.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 

05102017, 08:30 PM
(This post was last modified: 05102017 08:34 PM by Dieter.)
Post: #14




RE: Deltadays and date functions.
(05102017 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 builtin functions don't. Dieter 

05112017, 11:33 AM
(This post was last modified: 05112017 11:34 AM by Vtile.)
Post: #15




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


05112017, 04:12 PM
Post: #16




RE: Deltadays and date functions.
Dieter didn't say dates before January 1 1900 but before March 1 1900.
Andi 

05112017, 04:17 PM
Post: #17




RE: Deltadays and date functions.  
05112017, 05:03 PM
Post: #18




RE: Deltadays and date functions.
(05112017 11:33 AM)Vtile Wrote: Quickly to excel ... Atleast this new Office365 version I have at my disposal does give #valueerror for dates before 1.1.1900 when trying to apply ie. Dates, or DATEVALUE functions (nonVBA 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 123 and MS Multiplan appeared in the early Eighties, about the same time as the Commodore 64 and the HP41CX. #) Dieter 

05112017, 05:14 PM
Post: #19




RE: Deltadays and date functions.
(05112017 05:03 PM)Dieter Wrote:(05112017 11:33 AM)Vtile Wrote: Quickly to excel ... Atleast this new Office365 version I have at my disposal does give #valueerror for dates before 1.1.1900 when trying to apply ie. Dates, or DATEVALUE functions (nonVBA functions) to it. I'm surprised MS didn't lobby to have 1900 officially changed to be a leap year! Tom L Tom L Cui bono? 

05112017, 11:45 PM
Post: #20




RE: Deltadays and date functions.
The HP01 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 That's one small step for a man  one giant leap for mankind. 

« Next Oldest  Next Newest »

User(s) browsing this thread: