PDA

Vollständige Version anzeigen : Feiertage berechnen [Ostersonntag?]


SubLexx
15.07.2014, 10:24
Hallo ^^

man kann ja in Excel die beweglichen Feiertage berechnen lassen da diese sich ja auf den Ostersonntag beziehen und Formeln dazu findet man ja zu genüge im Internet (K1 = Jahreszahl).

=DM((TAG(MINUTE($K$1/38)/2+55)&".4."&$K$1)/7;)*7-6

Für 2014 funktioniert es soweit außer das es sich um 1 Tag verschiebt und da hab ich am Ende einfach noch ein "+1" darangehängt ^^'
Aber für 2015 verschiebt sich der Ostersonntag von der Rechnung um 6 Tage O_o?

Ostersonntag 2015 = 5. April 2015
Excel Rechnung = 11. April 2015

Ist die Formel doch so nicht richtig? :x

Hajo_Zi
15.07.2014, 10:28
ich benutze eine andere Formel, wir sehen ja nicht was in K1 steht.
<br/><b><em>Frei</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:100px;" /><col style="width:227px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>D</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; "><b>Jahr</b></td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Arbeitstage</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">das Wort Feiertag ist im VBA Code auch zur Zeitberechnung festgelegt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01.01.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Neujahr</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">06.01.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">hl. 3 Könige</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">03.03.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Rosenmontag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">18.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Karfreitag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">19.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Ostersamstag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">20.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Ostersonntag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">21.04.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Ostermontag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01.05.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Maifeiertag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">29.05.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Christi Himmelfahrt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">11.05.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Muttertag Formel von M.I.Nitraum</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">07.06.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Pfingstsamstag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">08.06.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Pfingstsonntag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">15</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">09.06.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Pfingstmontag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">16</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">19.06.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Fronleichnam Bayern</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">17</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">15.08.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Maria Himmelfahrt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">18</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">03.10.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Nationalfeiertag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">19</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">05.10.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Reformationstag laut Hinweis im Forum gibt es noch unterschiede in den Konfesio n (evangelich / katolisch)</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">20</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">31.10.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Reformationstag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">21</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01.11.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Allerheiligen</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">22</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">16.11.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Volkstrauertag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">23</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">19.11.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Buss- und Bettag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">24</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">23.11.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Totensonntag/Ewigkeitssontag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">25</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">30.11.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">1. Advent</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">26</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">07.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">2. Advent</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">27</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">3. Advent</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">28</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">21.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">4. Advent</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">29</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">24.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">hl. Abend</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">30</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">25.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">1. Weihnachtstag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">31</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">26.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">2. Weihnachtstag</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">32</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">31.12.2014</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Silvester</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>A3</td><td>=DATWERT("01.01."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A4</td><td>=DATWERT("06.01."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A5</td><td>=A8-48</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A6</td><td>=A8-2</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A7</td><td>=A8-1</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A8</td><td>=DM((TAG(MINUTE(B1/38)/2+55) &amp; ".4." &amp; B1)/7;)*7-WENN(JAHR(1)=1904;5;6)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A9</td><td>=A8+1</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A10</td><td>=DATWERT("01.05."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A11</td><td>=A8+39</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A12</td><td>=DATUM(B1;5;1)+15-WOCHENTAG(DATUM(B1;5;1))</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A13</td><td>=A8+48</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A14</td><td>=A8+49</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A15</td><td>=A8+50</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A16</td><td>=A8+60</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A17</td><td>=DATUM(B1;8;15)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A18</td><td>=DATWERT("03.10."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A19</td><td>=DATUM(B1;10;1)+7-WOCHENTAG(DATUM(B1;10;1);2)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A20</td><td>=DATWERT("31.10."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A21</td><td>=DATWERT("01.11."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A22</td><td>=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-35</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A23</td><td>=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-32</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A24</td><td>=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-28</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A25</td><td>=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-21</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A26</td><td>=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-14</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A27</td><td>=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-7</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A28</td><td>=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A29</td><td>=DATWERT("24.12."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A30</td><td>=DATWERT("25.12."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A31</td><td>=DATWERT("26.12."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>A32</td><td>=DATWERT("31.12."&amp;B1)</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.08 einschl. 64 Bit</td></tr></table><br/>

<img src="http://Hajo-Excel.de/images/grusz1.gif" align="middle" height="40" alt="Grußformel"><a href="http://Hajo-Excel.de/index.htm"><img border="0" src="http://Hajo-Excel.de/images/logo_hajo3.gif" align="middle" height="40" alt="Homepage"></a>

SubLexx
15.07.2014, 10:35
Dann nehm ich halt eine andere Formel. Dankeschön :)