Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
23.04.2024 16:27:34
Excel Starthinweis - Arbeitsmappenleistung überprüfen ...
23.04.2024 16:15:16
Mittels VBA vordefinierte Kommentare in Zellen einfügen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1368to1372
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1368to1372
1368to1372
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Datum berechnen

Datum berechnen
14.07.2014 19:42:11
Jenny
Hallo alle zusammen,
bitte helft mir
gibt es eine Formel, die mir berechnen kann, welcher vom heutigen Datum (Montag, 14.7.) berechnen kann, welcher der 35. Dienstag ist, jedoch dabei Feiertage, in nRW, die auf einen Dienstag fallen, nicht mitzählt? (Feiertage sind 3.10., 1.11., 25.12., 26.12., 1.1., 3.4., 6.4., 1.5.), hoffe das reicht.
Hat da jemand eine Idee?
Danke
Jenny

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Datum berechnen
15.07.2014 08:01:23
Ralf
Hallo Jenny,
vllt kann dir jemand eine Formel basteln.
Aber mMn ist es besser eine Funktion dafür zu nehmen.
Die allseits bekannte und im Netz zu findende Feiertags-Funktion hilft dir hier.
Passe die Feiertage in deinem BL einfach an, indem du das Sternchen in den Texten hinzufügst oder entfernst.
In einer Formel kannst du die Funktion ebenso benutzen:
B1:=Feiertag("A1")
wenn in A1 ein Datum steht.
Wenn du das direkt ohne weitere Berechnungen in einer Formel ausgeben möchtest, musst du eine weitere Funktion um die Feiertags-Funktion rumbauen, oder diese ergänzen.
Gruß
Ralf
Function Feiertag(Datum As Date) As String Dim J%, D% Dim O As Date J = Year(Datum) D = (((255 - 11 * (J Mod 19)) - 21) Mod 30) + 21 O = DateSerial(J, 3, 1) + D + (D > 48) + 6 - ((J + J \ 4 + D + (D > 48) + 1) Mod 7) Select Case Datum Case Is = DateSerial(J, 1, 1) Feiertag = "Neujahr" Case Is = DateSerial(J, 1, 6) Feiertag = "Heilige Dreikönige*" Case Is = DateAdd("D", -2, O) Feiertag = "Karfreitag" Case Is = O Feiertag = "Ostersonntag" Case Is = DateAdd("D", 1, O) Feiertag = "Ostermontag" Case Is = DateSerial(J, 5, 1) Feiertag = "1. Mai" Case Is = DateAdd("D", 39, O) Feiertag = "Christi Himmelfahrt" Case Is = DateAdd("D", 49, O) Feiertag = "Pfingstsonntag" Case Is = DateAdd("D", 50, O) Feiertag = "Pfingstmontag" Case Is = DateAdd("D", 60, O) Feiertag = "Fronleichnam*" Case Is = DateSerial(J, 8, 15) Feiertag = "Maria Himmelfahrt*" Case Is = DateSerial(J, 10, 3) Feiertag = "Deutsche Einheit" Case Is = DateSerial(J, 11, 22) - (DateSerial(J, 11, 18) Mod 7) Feiertag = "Buß- und Bettag*" Case Is = DateSerial(J, 10, 31) Feiertag = "Reformationstag*" Case Is = DateSerial(J, 11, 1) Feiertag = "Allerheiligen*" Case Is = DateSerial(J, 12, 24) Feiertag = "Heilig Abend*" Case Is = DateSerial(J, 12, 25) Feiertag = "1. Weihnachtsfeiertag" Case Is = DateSerial(J, 12, 26) Feiertag = "2. Weihnachtsfeiertag" Case Is = DateSerial(J, 12, 31) Feiertag = "Silvester*" Case Else Feiertag = "" End Select End Function

Anzeige
AW: Datum berechnen
15.07.2014 08:41:12
Ralf
Tue diese und die obige Function in ein Modul (ALT+F11)
die Formelsyntax: =AnzahlTage(AbWelchemDatumSollGezähltWerden=Zellbezug als Datum; DerWieVielteTagWirdGesucht=Zahl; WelcherWochenTag=1(Mo)-7(So))
Diese Funktion ist also allgemein für alle gleichartigen Probleme gedacht.
Gruß
Ralf

Function AnzahlTage(AbDatum As Date, DerWievielte As Long, Wochentag As Byte) As Date
Dim i As Long
If Wochentag  7 Then
AnzahlTage = "#NV"
Else
For i = 0 To DerWievielte
AbDatum = AbDatum + 1
Do Until Weekday(AbDatum, vbMonday) = Wochentag And Feiertag(AbDatum) = "" And InStr(1,  _
Feiertag(AbDatum), "*", vbTextCompare) = 0
'        Debug.Print Weekday(AbDatum, vbMonday)
AbDatum = AbDatum + 1
Loop
Debug.Print Weekday(AbDatum, vbMonday), AbDatum, i
Next
AnzahlTage = AbDatum
End If
End Function

Anzeige
AW: Datum berechnen
15.07.2014 08:16:04
UweD
Hallo
so?
Die Feiertage stehen in Spalte D
Tabelle1
 ABCDE
1Datum16.07.2014  Feiertage 2014 
2Wochentag3  Neujahr:Mi 01.01.2014
3      Karfreitag:Fr 18.04.2014
4addiere x Wochen35  Ostermontag:Mo 21.04.2014
5      Tag der Arbeit:Do 01.05.2014
6Zieltag24.03.2015  Christi Himmelfahrt:Do 29.05.2014
7Wochentag2  Pfingstmontag:Mo 09.06.2014
8      Fronleichnam:Do 19.06.2014
9      Tag der Deutschen Einheit:Fr 03.10.2014
10      Allerheiligen:Sa 01.11.2014
11      1. Weihnachtstag:Do 25.12.2014
12      2. Weihnachtstag:Fr 26.12.2014
13      DummydienstagDi 22.04.2014

verwendete Formeln
Zelle Formel Bereich
B2 =WOCHENTAG(B1;2) 
B6 =B1+B4*7-B2+2+SUMMENPRODUKT((WOCHENTAG(E:E;2)=2)*1)*7 
B7 =WOCHENTAG(B6;2) 




zur Kontrolle mal noch das hier:
Tabelle1
 FGHIJ
1Anzahl Dienstage1  Kontrolle15.07.2014
2      122.07.2014
3      229.07.2014
4      305.08.2014
5      412.08.2014
6      519.08.2014
7      626.08.2014
8      702.09.2014
9      809.09.2014
10      916.09.2014
11      1023.09.2014
12      1130.09.2014
13      1207.10.2014
14      1314.10.2014
15      1421.10.2014
16      1528.10.2014
17      1604.11.2014
18      1711.11.2014
19      1818.11.2014
20      1925.11.2014
21      2002.12.2014
22      2109.12.2014
23      2216.12.2014
24      2323.12.2014
25      2430.12.2014
26      2506.01.2015
27      2613.01.2015
28      2720.01.2015
29      2827.01.2015
30      2903.02.2015
31      3010.02.2015
32      3117.02.2015
33      3224.02.2015
34      3303.03.2015
35      3410.03.2015
36      3517.03.2015
37      3624.03.2015

verwendete Formeln
Zelle Formel Bereich
G1 =SUMMENPRODUKT((WOCHENTAG(E:E;2)=2)*1) 




Gruß UweD

Anzeige
AW: Datum berechnen
15.07.2014 08:18:42
UweD
Die Feiertage stehen in Spalte D natürlich in Spalte E

AW: Datum berechnen
15.07.2014 08:47:46
UweD
Nochmal automatisierte Feiertage... Format= "TTT TT.MM.JJJJ"
Andere Feiertage fallen per Definition NIE auf Dienstage (Ostermontag, Kafreitag usw.) und können ignoriert werden.
Die Feiertage des Folgejahres auch noch ergänzt. Für den Fall, wenn die +35 Wochen über den Jahreswechsel gehen....
Tabelle1
 ABCDE
1DatumMi 15.10.2014  Feiertage2014
2      Neujahr:Mi 01.01.2014
3      Tag der Arbeit:Do 01.05.2014
4addiere x Wochen35  Tag der Deutschen Einheit:Fr 03.10.2014
5      Allerheiligen:Sa 01.11.2014
6ZieltagDi 16.06.2015  1. Weihnachtstag:Mi 24.12.2014
7      2. Weihnachtstag:Do 25.12.2014
8      Neujahr:Do 01.01.2015
9      Tag der Arbeit:Fr 01.05.2015
10      Tag der Deutschen Einheit:Sa 03.10.2015
11      Allerheiligen:So 01.11.2015
12      1. Weihnachtstag:Do 24.12.2015
13      2. Weihnachtstag:Fr 25.12.2015

verwendete Formeln
Zelle Formel Bereich
E1 =JAHR(HEUTE()) 
E2 =DATUM(E1;1;1) 
E3 =DATUM(E1;5;1) 
E4 =DATUM(E1;10;3) 
E5 =DATUM(E1;11;1) 
B6 =B1+B4*7-WOCHENTAG(B1;2)+2+SUMMENPRODUKT((WOCHENTAG(E:E;2)=2)*1)*7 
E6 =DATUM(E1;12;24) 
E7 =E6+1 
E8 =DATUM(E1+1;1;1) 
E9 =DATUM(E1+1;5;1) 
E10 =DATUM(E1+1;10;3) 
E11 =DATUM(E1+1;11;1) 
E12 =DATUM(E1+1;12;24) 
E13 =E12+1 




Gruß UweD

Anzeige
AW: Datum berechnen
15.07.2014 09:35:33
Jenny
Hallo Uwe,
hallo Ralf,
dankeschön. Uwe, dein Vorschlag ist mehr als super, den kann man auch für andere Kalendertage und andere Wochenanzahl benutzen.
Ralf, ich muss leider sagen, mit Makros kenne ich mich zu wenig aus, als das ich wüsste was ich damit machen muss, wenn ich deinen Vorschlag auf andere Beispiele anwenden möchte, daher werde ich wohl in Zukunft mit Uwes Vorschlag arbeiten. Trotzdem auch dir vielen Dank.
Gruß
Jenny

35=36? ...
15.07.2014 12:15:54
der
Hallo Uwe,
... dies ergibt Dein Nachweis. In Deiner Formel ist auch ein kleiner logischer Fehler, der zu 36 führen muss.
Ich muss jetzt allerdings gleich erst einmal offline gehen, melde mich aber später dazu noch einmal.
Gruß Werner
.. , - ...

Anzeige
AW: 35=36? ...
15.07.2014 14:18:43
Jenny
Hallo Uwe,
danke erstmal. Leider passt deine Berechnung nicht ganz, auch wenn ich mir den Rest jetzt auch mit Kopfrechnen zusammenreimen konnte, also keine Anpassung mehr nötig ist.
Ich hatte den 35. Dienstag vom gestrigen Tag an gesucht, also damit sollte heute der erste Dienstag sein, nicht erst wie in deiner Tabelle am 22.7. naja aber von deinem Ergebnis 7 Tage abziehen bekam ich auch so noch hin, musst also nichts mehr anpassen.
Gruß
Jenny

Anzeige
AW: 35=36? ...
15.07.2014 16:08:08
UweD
Ich nochmal
der nächste Wochentag wird jetzt richtig berücksichtigt.
Da ist noch Einiges mehr zu bedenken...
Feiertage dürfen ja nur berücksichtigt werden,
    wenn Sie &gt dem aktuellen Tag sind und auch nur,
    wenn sie kleiner dem theoretischen Zieltag sind
das könnte man ggf noch durch die Formel berücksichtigen.
=B1-WOCHENTAG(B1;2)+B6+WENN(WOCHENTAG(B1;2)<B6;-7;0)+B2*7+SUMMENPRODUKT((WOCHENTAG(E:E;2)=B6) *(E:E<=B1+B2*7)*(E:E>=B1)*1) *7
Das ist aber auch nur die halbe Wahrheit.
Wäre der neue Zieltag dann wieder ein Feiertag, müsste dieser wieder mitberücksichtigt werden usw..
Also eine rekursive Betrachtung.

Anzeige
beschränkt "rekursive" Betrachtung ...
15.07.2014 18:46:45
der
Hallo Uwe,
... sorry, wenn ich erst jetzt dazukomme wieder zuschreiben. Aber die entscheidenden Abweichungen hast Du ja mittlerweile auch selbst fixiert.
Ich hatte heute Mittag untenstehende noch nicht durchkonstruierte Formel aufgestellt. Musstte dann allerdings fort. Hatte aber schon bemerkt, dass es möglicherweise mit Aufwand möglich, allgemein die möglichen Feiertagsverlängerungen richtig einzuarbeiten und das möglichst ohne dass die Feiertagsliste auf Jahre voraus schon geschrieben werden muss.
 DEF
1DatumMo 14.07.2014Mi 01.01.2014
2Wochentag3Do 03.04.2014
3x Wochen35Fr 18.04.2014
4ErgebnisdatumDi 10.03.2015Mo 21.04.2014
5  Do 01.05.2014
6  Do 29.05.2014
7  Fr 03.10.2014
8  Sa 01.11.2014
9  Do 25.12.2014
10  Fr 26.12.2014
11  Do 01.01.2015

Formeln der Tabelle
ZelleFormel
E4=E1+3-REST(E1;7)+(E$3-1+(REST(E1;7)>=3)+SUMMENPRODUKT((FT>=E1)*(FT<=E1+3-REST(E1;7)+(E$3-1+(REST(E1;7)>=3))*7)*(REST(FT;7)=3)))*7
Namen in Formeln
ZelleNameBezieht sich auf
E4FT=Tab_1!$F$1:$F$11
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Eine allgemeingültige Formel zu entwickeln, halte ich momentan für zu aufwendig. Aber interessant wäre es vielleicht schon. Möglicherweise liest dies ja Sepp ... und vielleicht reizt ihn das Problem.
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige