Anzeige
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 Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Datum berechnen

Forumthread: 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

Anzeige

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

Anzeige
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
.. , - ...

AW: 35=36? ...
15.07.2014 13:06:02
UweD
Hier nochmal als Datei mit weiteren Verbesserungen.
Ich denke das passt so.... Siehe Kontrollbereich
https://www.herber.de/bbs/user/91508.xlsx
Gruß UweD

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
;

Forumthreads zu verwandten Themen

Enddatum berechnen ohne Add in
Silvi

Hi,
steh auf dem Schlauch... brauche eine Formel die mir das Enddatum berechnet. Also, in Zelle B4 steht das Anfangsdatum und es sollen 3 Tage dazu gerechnet werden, wenn ein Wochenende dazwischen liegt oder/und ein Feiertag, muß sich der Zeitraum natürlich entsprechend verlängern, das ganz...
17.11.2008 08:52:35
Thread-ID: 1025069
3 Beiträge
Datum berechnen wenn ...
Christian

Hallo
Ich stehe vor einem für mich unlösbarem problem.
Ich brauche dringend eure Hilfe. Ich bin neu hier und hab mich extra für dieses
problem registriert.
Ich habe eine Spalte in der ich das fortlaufende Datum stehen habe. in einer
anderen Spalte daneben habe ich für jedes...
15.03.2009 14:22:57
Thread-ID: 1060411
14 Beiträge
Datum berechnen
Sibylle

Hallo,
das Auswanderungsdatum einer Person war zB. der 2. April 1874.
Die Person soll damals 27 1/2 Jahre alt gewesen sein.
Wie kann man mit Excel das Geburtsdatum ermitteln, also eine Berechnung vor dem 1.1.1900 vornehmen?
Gruß
Sibylle
07.11.2009 19:06:59
Thread-ID: 1115258
4 Beiträge
Datum berechnen
Jan C.

Guten morgen,
ich habe eine Tabelle mit 2 Spalten, in Spalte Nr.1 werden Daten per Userform eingegeben, in Spalte zwei (ebenfalls per Userform) "Haltbarkeitsfristen", z.B. 3 Tage. In Spalte drei soll nun praktisch das "Haltbarkeitsdatum" ausgegeben werden, also einfach eine Summe der Werte...
21.05.2002 10:03:22
Thread-ID: 122326
2 Beiträge
Datum berechnen
Wolfgang

Hallo,
in der TextBox5 befindet sich ein Datum und in TextBox7 (jeweils auf einem UF) die Angabe in Monaten. Wie kann ich erreichen, dass in TextBox6 das errechnete Datum -TextBox5 + Dauer in Monaten- erscheint. Danke schon jetzt für die Rückmeldungen.
Gruß - Wolfgang
01.12.2011 13:06:07
Thread-ID: 1240236
3 Beiträge
Datum berechnen
Savarol

Hai Leute, ich weiss, dass das nicht hier her gehört, aber vielleicht kann mir trotzdem jemand helfen. Ich möchte in Word zu einem Datum ein paar Tage addieren. Z. B. zu {Zeit \@ "tt.mm.jjjj"} 5 Tage.Grüße Savarol
06.06.2002 08:33:08
Thread-ID: 126361
2 Beiträge
Datum berechnen
stefan

Hallo zusammen,
ich brauche eure Hilfe ich habe ein festes Datum z.b. 12.10.2012 das liegt immer in der Zukunft
was ich erreichen will ist wenn das Datum 3 Monate vor dem eigentlichen Datum liegt bitte die Zelle Rot
markieren bei 5 Monaten gelb und 6 Monate grün werden.
Vielen...
26.04.2012 15:19:44
Thread-ID: 1261123
2 Beiträge
Anzeige
Optimales Datum berechnen
Daniel

Hallo zusammen,
ich würde mir gerne ein optimales Datum beim Komissionieren von Aufträgen bererchnen lassen.
Ein Komissionierer startet die Bearbeitung des Auftrages am 19.09.2013 11:12 Uhr.
Aufträge die morgens begonnen werden, müssen bis 13:00 am gleichen abgeschlossen sein. Auftr...
11.10.2013 11:23:10
Thread-ID: 1334062
4 Beiträge
Anzeige
Enddatum berechnen
ludmila
Hallo,
anbei meine Mappe zur Berechnung eines Enddatums.
Wenn ich unter Anzahl Tage eine Zahl eingebe sollte als Enddatum folgendes
berücksichtigt werden.
Mit Samstag
abzüglich der Feiertage
Bei meinem Beispiel müsste das Enddatum der 02.05.2015 sein.
Danke!
Gruß
...
03.01.2015 10:42:56
Thread-ID: 1400524
5 Beiträge
Datum berechnen
baumi

Hallo,
habe eine Liste mit Daten,
A2 = Datum (10.2.15)
B2 = Anzahl von(zB. 10) Tagen (unterschiedlich)
C2 = Datum HEUTE()
Die Tage sollen hierbei langsam weniger werden
Ergebnis soll sein der 20.2.15 (fällig), dabei soll das Datum durch "fällig" ersetzt werden.
wei...
12.02.2015 19:28:54
Thread-ID: 1409152
6 Beiträge
Datum berechnen (Monatsende)
Helmut

Guten Abend da draußen liebes Forum
Ein einfaches Problem für wem der sich wirklich auskennt....
In Zelle A1 steht das Datum 01.01.2015. Von A2 bis A32 stehen jeweils die Tage von 01.01.2015(=A2) bis 31.01.2015(=A32). Nun kommt das Monatsende und es soll sich nur das Jahr für das vergan...
23.02.2015 22:41:18
Thread-ID: 1411075
12 Beiträge
Datum berechnen
Christian

Hallo, gibt es eine Formel die aus mehreren Daten eines zusammensetzen kann? jeweils der höchste Tag, der höchste Monat und das höchste Jahr zu einem Datum zusammengefasst?
z.B.
3.2.2001
2.3.2005
1.9.2002
soll zusammengefasst werden als 3.9.2005
Versteht ihr was ich mein...
05.04.2015 14:25:16
Thread-ID: 1419153
9 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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