Microsoft Excel

Herbers Excel/VBA-Archiv

Wenn Feiertag dann 7 Tage später | Herbers Excel-Forum


Betrifft: Wenn Feiertag dann 7 Tage später von: Heinz H
Geschrieben am: 17.11.2009 11:48:10

Hallo Leute

Ich habe in A2:A17 das Datum für Feiertage stehen.
In F2:F18 jeden 2.Mittwoch im Monat.

Nun möchte ich, wenn ein Mittwoch auf einen Feiertag fällt,das mir das Datum des nachfolgende Mittwochs
angezeigt wird. Also Plus 7 Tage.

Könnte mir bitte jemand weiterhelfen?

Gruß
Heinz

Feiertage-Urlaub

 ABCDEFG
2Fr .01.01.2010    Neujahr  Mi.13.Jänner.2010 
3Mi .06.01.2010    3 Könige  Mi.10.Februar.2010 
4So .04.04.2010    Ostersonntag  Mi.10.März.2010 
5Mo .05.04.2010    Ostermontag  Mi.07.April.2010 
6Sa .01.05.2010    1. Mai  Mi.05.Mai.2010 
7Do .13.05.2010    Chr.Himmelfahrt  Mi.02.Juni.2010 
8Mo .24.05.2010    Pfingstmontag  Mi.30.Juni.2010 
9Do .03.06.2010    Fronleichnam  Mi.28.Juli.2010 
10So .15.08.2010    Mariä Himmelf.  Mi.25.August.2010 
11Di .26.10.2010    Nat.Feiertag (Ö)  Mi.22.September.2010 
12Mo .01.11.2010    Allerheiligen  Mi.20.Oktober.2010 
13Mi .08.12.2010    Mariä Empfängnis  Mi.17.November.2010 
14Fr .24.12.2010    Hl.Abend    x
15Sa .25.12.2010    1. Weihnachtstag    x
16So .26.12.2010    2. Weihnachtstag    x
17Fr .31.12.2010    Silvester    x
18            x

verwendete Formeln
Zelle Formel
A2 =DATWERT("01.01."&B1)
B2 =DATWERT("01.01."&B1)
F2 =WENN(G2="x";"";A2-1-WOCHENTAG(A2-3;3)+7*2)
A3 =DATWERT("06.01."&B1)
B3 =DATWERT("06.01."&B1)
F3 =WENN(G3="x";"";F2+28)
A4 =DM((TAG(MINUTE(B1/38)/2+55) & ".4." & B1)/7;)*7-WENN(JAHR(1)=1904;5;6)
B4 =DM((TAG(MINUTE(B1/38)/2+55) & ".4." & B1)/7;)*7-WENN(JAHR(1)=1904;5;6)
F4 =WENN(G4="x";"";F3+28)
A5 =A4+1
B5 =B4+1
F5 =WENN(G5="x";"";F4+28)
A6 =DATWERT("01.05."&B1)
B6 =DATWERT("01.05."&B1)
F6 =WENN(G6="x";"";F5+28)
A7 =A4+39
B7 =B4+39
F7 =WENN(G7="x";"";F6+28)
A8 =A4+50
B8 =B4+50
F8 =WENN(G8="x";"";F7+28)
A9 =A4+60
B9 =B4+60
F9 =WENN(G9="x";"";F8+28)
A10 =DATWERT("15.8."&B1)
B10 =DATWERT("15.8."&B1)
F10 =WENN(G10="x";"";F9+28)
A11 =DATWERT("26.10."&B1)
B11 =DATWERT("26.10."&B1)
F11 =WENN(G11="x";"";F10+28)
A12 =DATWERT("01.11."&B1)
B12 =DATWERT("01.11."&B1)
F12 =WENN(G12="x";"";F11+28)
A13 =DATWERT("8.12."&B1)
B13 =DATWERT("8.12."&B1)
F13 =WENN(G13="x";"";F12+28)
A14 =DATWERT("24.12."&$B$1)
B14 =DATWERT("24.12."&$B$1)
F14 =WENN(G14="x";"";F13+28)
A15 =DATWERT("25.12."&B1)
B15 =DATWERT("25.12."&B1)
F15 =WENN(G15="x";"";F14+28)
A16 =DATWERT("26.12."&B1)
B16 =DATWERT("26.12."&B1)
F16 =WENN(G16="x";"";F15+28)
A17 =DATWERT("31.12."&$B$1)
B17 =DATWERT("31.12."&$B$1)
F17 =WENN(G17="x";"";F16+28)
F18 =WENN(G18="x";"";F17+28)

Zahlenformate
Zelle Format Wert
A2 TTT .TT.MM.JJJJ40179
B2 TTT .TT.MM.JJJJ40179
D2 @Neujahr
F2 TTT.TT.MMMM.JJJJ40191
G2 TT.MMM.JJ 
A3 TTT .TT.MM.JJJJ40184
B3 TTT .TT.MM.JJJJ40184
D3 @3 Könige
F3 TTT.TT.MMMM.JJJJ40219
G3 TT.MMM.JJ 
A4 TTT .TT.MM.JJJJ40272
B4 TTT .TT.MM.JJJJ40272
D4 @Ostersonntag
F4 TTT.TT.MMMM.JJJJ40247
G4 TT.MMM.JJ 
A5 TTT .TT.MM.JJJJ40273
B5 TTT .TT.MM.JJJJ40273
D5 @Ostermontag
F5 TTT.TT.MMMM.JJJJ40275
G5 TT.MMM.JJ 
A6 TTT .TT.MM.JJJJ40299
B6 TTT .TT.MM.JJJJ40299
D6 @1. Mai
F6 TTT.TT.MMMM.JJJJ40303
G6 TT.MMM.JJ 
A7 TTT .TT.MM.JJJJ40311
B7 TTT .TT.MM.JJJJ40311
D7 @Chr.Himmelfahrt
F7 TTT.TT.MMMM.JJJJ40331
G7 TT.MMM.JJ 
A8 TTT .TT.MM.JJJJ40322
B8 TTT .TT.MM.JJJJ40322
D8 @Pfingstmontag
F8 TTT.TT.MMMM.JJJJ40359
G8 TT.MMM.JJ 
A9 TTT .TT.MM.JJJJ40332
B9 TTT .TT.MM.JJJJ40332
D9 @Fronleichnam
F9 TTT.TT.MMMM.JJJJ40387
G9 TT.MMM.JJ 
A10 TTT .TT.MM.JJJJ40405
B10 TTT .TT.MM.JJJJ40405
D10 @Mariä Himmelf.
F10 TTT.TT.MMMM.JJJJ40415
G10 TT.MMM.JJ 
A11 TTT .TT.MM.JJJJ40477
B11 TTT .TT.MM.JJJJ40477
D11 @Nat.Feiertag (Ö)
F11 TTT.TT.MMMM.JJJJ40443
G11 TT.MMM.JJ 
A12 TTT .TT.MM.JJJJ40483
B12 TTT .TT.MM.JJJJ40483
D12 @Allerheiligen
F12 TTT.TT.MMMM.JJJJ40471
A13 TTT .TT.MM.JJJJ40520
B13 TTT .TT.MM.JJJJ40520
D13 @Mariä Empfängnis
F13 TTT.TT.MMMM.JJJJ40499
A14 TTT .TT.MM.JJJJ40536
B14 TTT .TT.MM.JJJJ40536
D14 @Hl.Abend
F14 TTT.TT.MMMM.JJJJ 
G14 TT.MMM.JJx
A15 TTT .TT.MM.JJJJ40537
B15 TTT .TT.MM.JJJJ40537
D15 @1. Weihnachtstag
F15 TTT.TT.MMMM.JJJJ 
G15 TT.MMM.JJx
A16 TTT .TT.MM.JJJJ40538
B16 TTT .TT.MM.JJJJ40538
D16 @2. Weihnachtstag
F16 TTT.TT.MMMM.JJJJ 
G16 TT.MMM.JJx
A17 TTT .TT.MM.JJJJ40543
B17 TTT .TT.MM.JJJJ40543
D17 @Silvester
F17 TTT.TT.MMMM.JJJJ 
G17 TT.MMM.JJx
F18 TTT.TT.MMMM.JJJJ 
G18 TT.MMM.JJx
Zellen mit Format Standard werden nicht dargestellt

Tabellendarstellung in Foren Version 4.31


  

Betrifft: mit einer Formel (zwar noch nicht optimiert), ... von: neopa
Geschrieben am: 17.11.2009 12:41:23

Hallo Heinz,

... ist Dein Wunsch realisierbar. Das allerdings für Deine verbale Vorgabe:

In F2:F18 jeden 2.Mittwoch im Monat.
welche im Widerspruch zu Deinen Angaben in Deiner Tabelle steht.

Die Formel F2 einfach nach unten kopieren:

 AF
1 2010
2Fr 01.01.2010Mi 13.01.2010
3Mi 06.01.2010Mi 10.02.2010
4So 04.04.2010Mi 10.03.2010
5Mo 05.04.2010Mi 14.04.2010
6Sa 01.05.2010Mi 12.05.2010
7Do 13.05.2010Mi 09.06.2010
8Mo 24.05.2010Mi 14.07.2010
9Do 03.06.2010Mi 11.08.2010
10So 15.08.2010Mi 08.09.2010
11Di 26.10.2010Mi 13.10.2010
12Mo 01.11.2010Mi 10.11.2010
13Mi 08.12.2010Mi 15.12.2010
14Fr 24.12.2010 
15Sa 25.12.2010 
16So 26.12.2010 
17Fr 31.12.2010 
18  

Formeln der Tabelle
ZelleFormel
F2=DATUM(F$1;ZEILE(A1); 1)+11+7*((REST(DATUM(F$1;ZEILE(A1); 1); 7)>4)+ISTZAHL(VERGLEICH(DATUM(F$1;ZEILE(A1); 1)+11+7*(REST(DATUM(F$1;ZEILE(A1); 1); 7)>4)-REST(DATUM(F$1;ZEILE(A1); 1); 7); A:A;))) -REST(DATUM(F$1;ZEILE(A1); 1); 7)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Gruß Werner
... , - ..


  

Betrifft: AW: Wenn Feiertag dann 7 Tage später von: Rudi Maintaire
Geschrieben am: 17.11.2009 12:52:16

Hallo,
in F2 und runterkopieren:

=DATUM(JAHR($A$2);ZEILE(A1);1)-REST(DATUM(JAHR($A$2);ZEILE(A1);1)-5;7)+13+7*(ZÄHLENWENN($A$2:$A$17; DATUM(JAHR($A$2);ZEILE(A1);1)-REST(DATUM(JAHR($A$2);ZEILE(A1);1)-5;7)+13)>0) 

Gruß
Rudi


  

Betrifft: AW: Danke an Neopa & Rudi von: Heinz H
Geschrieben am: 17.11.2009 12:56:36

Hallo Ihr beiden

Ich danke euch recht herzlich !!

Funkt. wie gewünscht.

Gruß
Heinz


  

Betrifft: AW: Wenn Feiertag dann 7 Tage später von: fcs
Geschrieben am: 17.11.2009 13:30:13

Hallo Heinz,

für den jweils 2. Mittwoch im Monat kannst du dir die Prüfung fast sparen in Östereich wäre davon nur der 8. Dezember betroffen.

Gruß
Franz

Ansonsten hier die Formeln zur Berechnung:

     F         	G              	H
              	2 .            	2010
1. des Monats	Mi im Monat	mit Verschiebung
01.01.2010	Mi 13.Jan 2010	Mi 13.Jan 2010
01.02.2010	Mi 17.Feb 2010	Mi 17.Feb 2010
01.03.2010	Mi 17.Mrz 2010	Mi 17.Mrz 2010
01.04.2010	Mi 14.Apr 2010	Mi 14.Apr 2010
01.05.2010	Mi 12.Mai 2010	Mi 12.Mai 2010
01.06.2010	Mi 16.Jun 2010	Mi 16.Jun 2010
01.07.2010	Mi 14.Jul 2010	Mi 14.Jul 2010
01.08.2010	Mi 11.Aug 2010	Mi 11.Aug 2010
01.09.2010	Mi 08.Sep 2010	Mi 08.Sep 2010
01.10.2010	Mi 13.Okt 2010	Mi 13.Okt 2010
01.11.2010	Mi 17.Nov 2010	Mi 17.Nov 2010
01.12.2010	Mi 08.Dez 2010	Mi 15.Dez 2010
Formeln:
F3: =DATUM($H$1;1;1)
G3: =F3-WOCHENTAG(F3;2)+3+($G$1-(WOCHENTAG(F3;2)=3)*1)*7
H3: =WENN(ISTFEHLER(VERGLEICH(G3;$A$2:$A$18;0));G3;G3+7)
F4: =DATUM($H$1;2;1)
G4: =F4-WOCHENTAG(F4;2)+3+($G$1-(WOCHENTAG(F4;2)=3)*1)*7
H4: =WENN(ISTFEHLER(VERGLEICH(G4;$A$2:$A$18;0));G4;G4+7)



  

Betrifft: AW: Wenn Feiertag dann 7 Tage später von: Heinz H
Geschrieben am: 17.11.2009 13:46:48

Hallo Rudi

Es könnte auch noch Maria Himmelfahrt 15.8 betroffen sein.

So ist es 100%ig sicher das kein Feiertag kollidiert.
Aber dennoch DANKE

Gruß
Heinz


  

Betrifft: AW: Sorry - Blödsinn von mir von: Heinz H
Geschrieben am: 17.11.2009 13:49:29

Sorry Rudi

War ein grosser Blödsinn von mir !!

Du hast natürlich recht !!!

Gruß
Heinz


  

Betrifft: AW: Sorry - Blödsinn von mir von: fcs
Geschrieben am: 17.11.2009 14:12:25

Hallo Heinz,

passiert manchmal.

Wie bei meiner Mittwochsformel. Da iat auch noch eine Fehler drin.
korrekt:

=F3-WOCHENTAG(F3;2)+3+($G$1-1*(WOCHENTAG(F3;2)<=3))*7
Gruß
Franz


  

Betrifft: AW: Sorry - Blödsinn von mir von: Heinz H
Geschrieben am: 17.11.2009 14:15:53

Hallo Franz

Recht herzlichen Dank.

Gruß
Heinz


Beiträge aus den Excel-Beispielen zum Thema "Wenn Feiertag dann 7 Tage später"