Specific AOO calendar

[Obi]

Hello,

now i'm hoping i do my post in the right section finally. Sorry if i overlooked the topic was already mentioned here in an earlier thread.

see attachment ->
saba2Day: I imitated an identic al Schedule-function

So an explanation:
365,25 days is the central idea because 2000 WAS in fact a leap year. So this is the only main reason that ca. 200 years can be supported. As you know of course every four years, the rounding of the ,25 is +1 more = a leap year.

I was stunned by the fact how easy it was to set up a calendar like this. Of course this is not very elegant (call the function 366 times;it ~ loads 3 seconds for a year), but it works. This possibilities are not only offered in the german OO versions, but also the american!

You start in your own function. German Bundes-Land,date number and year have already been transferred from the sheet, and you call GermanHolidays with two parameters.

I reckoned that both OOo and AOO in their oldest/youngest version take into account the German (Bank) Holiday, out of which i wrote the lines regarding the calendar.

It happens to be that this calendar macro is only in OO BASIC ("Schedule"-section), not in Libre.

Have a look at the year 2008, you'll find out that 1st of May has two events - just like equal to fourth of advent on 24 december.

You could for example also add a holiday like 17 June in Germany, which was from ~ 1954 until 1990 the national holiday,if you incorporate and make the schedule functions your own, loaded into an own project. Then,when two globals suffice, it's also possible to implement the old german 17.June (until 1990 national holiday) and not to have 366 procedure calls.

Question now: is it possible with only two globals instead of 366 function calls?

Greetings and Regards, Christian
--- --- --- --- --- --- --- --- --- ---
note football soccer table,write in BN220:
=IF(Z$2=1;IF(OR(ISERROR(BJ220+BM220-1);BM220="");"";BJ220+BM220-1);IF(Z$2=0;IF(OR(ISERROR(BB220+BE220-1);BE220="");"";BB220+BE220-1);"in Z2 1 or 0 only,please!"))
(drag downwards with AutoFill) (AZ instead of BB if you hadn't inserted that stuff,last topic)
this formula offers full functionality for leagues (Italy,Spain) with head-to-head criterion ruling, with a 0-1-switch you do in Z2.
With 'Swapping' (as described in my ancient .odt here) you can establish a situation to fit head-to-head criterion then.

[Lupp]

""As you know of course every four years, ... is a leap year.""

Do you know the correct rule which also states:
- If the number of the year is a multiple of 100, the leap day is omitted EXCEPT
- that number is a multiple of 400.

See https://en.wikipedia.org/wiki/Gregorian_calendar

This rule results in an average length of the year of exactly 365.2425 days which is very close to 365.2422 days (the four-decimals-value found by astronomy).

[Obi]

Yes, I heard about that
You're right, Lupp.
Thank you much for clarifying.
(i just was happy being able doing it (because of 2000);
fascinating that the used ,25 is just not odd for the computer,
although the algorithm could work with a CUT(0,8) f.e.,too
I can't remember exactly, because it was back in April,
but it was 365,3 | 365,55 | 365,8 | 366,05 then i think)
[EDITh: the name of this function is TRUNC,not CUT]
--- --- --- --- --- --- --- --- --- ---
note football soccer table,write in BN220:
=IF(Z$2=1;IF(OR(ISERROR(BJ220+BM220-1);BM220="");"";BJ220+BM220-1);IF(Z$2=0;IF(OR(ISERROR(BB220+BE220-1);BE220="");"";BB220+BE220-1);"in Z2 1 or 0 only,please!"))
(drag downwards with AutoFill) (AZ instead of BB if you hadn't inserted that stuff,last topic)
this formula offers full functionality for leagues (Italy,Spain) with head-to-head criterion ruling, with a 0-1-switch you do in Z2.
With 'Swapping' (as described in my ancient .odt here) you can establish a situation to fit head-to-head criterion then.

[MrProgrammer]

As you know of course every four years

Do you know the correct rule which also states:
- If the number of the year is a multiple of 100, the leap day is omitted EXCEPT
- that number is a multiple of 400.

Translation to StarBasic, passing 4-digit years to these functions:

Function IS_LEAP_YEAR(YYYY As Double) As Boolean
If YYYY Mod   4 Then IS_LEAP_YEAR = False : Exit Function  ' Example: 2025
If YYYY Mod 100 Then IS_LEAP_YEAR = True  : Exit Function  ' Example: 2024
If YYYY Mod 400 Then IS_LEAP_YEAR = False : Exit Function  ' Example: 2100
                     IS_LEAP_YEAR = True                   ' Example: 2000
End Function

Function DAYS_IN_YEAR(YYYY As Double) As Double
If IS_LEAP_YEAR(YYYY) Then DAYS_IN_YEAR = 366 Else DAYS_IN_YEAR = 365
End Function

Function DAYS_IN_MONTH(MM As Double,YYYY As Double) As Double
Select Case MM
   Case 4, 6, 9, 11           : DAYS_IN_MONTH = 30
   Case 1, 3, 5, 7, 8, 10, 12 : DAYS_IN_MONTH = 31
   Case Else
      If IS_LEAP_YEAR(YYYY) Then DAYS_IN_MONTH = 29 Else DAYS_IN_MONTH = 28
End Select
End Function

If you only need DAYS_IN_YEAR, use:

Function DAYS_IN_YEAR(YYYY As Double) As Double
If YYYY Mod   4 Then DAYS_IN_YEAR = 365 : Exit Function  ' Example: 2025
If YYYY Mod 100 Then DAYS_IN_YEAR = 366 : Exit Function  ' Example: 2024
If YYYY Mod 400 Then DAYS_IN_YEAR = 365 : Exit Function  ' Example: 2100
                     DAYS_IN_YEAR = 366                  ' Example: 2000
End Function

IS_LEAP_YEAR makes very few IF tests. For years like 2025, it only needs one test to determine that this is not a leap year. This happens ¾ of the time. Years like 2024 need two tests. Two suffice for any non-century year. Century years, need three tests, but these years are rare. The average number of tests, for a random year, is 1.26.

Calc provides spreadsheet functions ISLEAPYEAR, DAYSINYEAR, and DAYSINMONTH, though they need Calc dates as an argument, not years. You can create a Calc date from a year using =DATE(yyyy;1;1) or =DATE(yyyy;mm;1).

[Villeroy]

How about this one?

[DiGro]

How about this one?

Nice, thanks

[Obi]

Hello Again,

,I added a correction summand as mentioned, to go further to year 1800 (written in the seven cells it concerns ~C80, D50). 0,125 would be the theoretically 'safest' correction summand because of the boundaries, and is also the one which is 0,00I in the binary and therefore even to the machine at runtime; 0,1 has two digits less in the code - so one can choose which of them shall do the trick. I used 0,125 in the file. 0,05 and 0,15 and 0,2 also possible and everything between etc. (they don't make much sense (/ binary period long 20 and 4 respectively)).
The correction summand is necessary cause dates from 01/01/1897 to 02/28/1897 beginning,
would go wrong then because C79 was 1 too low then in this negative. Both TRUNC and ROUNDDOWN have the effect to always round towards the 0, in the direction of the 0 (also negative)!

Without more formulas, it's not possible to expand this range further to any date beyond 1800/01/03 and 2100/28/02 in this (duck) approach to calendar. (But,with some IF clauses (decrement), you can extend to e.g.2200)
(Just alike in SoccFootbTableVLOOKUP version, you can go a lot farther with a/n un/sorted list. Values stay even.)
( =IF(VLOOKUP(INT(V4/100)*100;S10:U18;3;0);60+1+VLOOKUP(INT(V4/100)*100;matrix;etc. Simply ask me for the formulas)
So the Rosenmontag (this is Carnival) 1800 therefore is wrong,including the obvious holiday things you see at the end/begin of the year and throughout the year (f.e.) for all of these outranged dates.

No one wants to be stuck or locked in this maze of 366 error messages. So I transferred the helper cells (named ranges) to another point. Please be careful and don't experiment with things in the code, i fixed obvious user errors that can occur in the sheet, but the code is hot! So it shouldn't be a matter for the task manager.

I mean it's an interesting thing,that. Greetings Chris

Uuups, I forgot about the inline matrices in C37 and F5... You can omit an OR with pipes in the C37
Note: these constructs are O.K.,too :
if saba2Day = 190 then
CalBankholidayName(190) = "USA Independaence Day"
end if
if Datumszahl2 = 43039 then
CalBankholidayName(310) = "Reformationstag 2017" '500th jubilee;holiday for all BundesLänder
end if
PS: the Date Format in the calc options should always be "12/30/1899" which is the default