Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1160to1164
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
1160to1164
1160to1164
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel für variable Datumsberechnung gesucht

Formel für variable Datumsberechnung gesucht
Ralf
Hallo Forum,
ich benötige eine Formel für die Berechnung folgender Tage:
- Datum für den 3. Samstag im Monat September
- Datum für Buß- und Bettag
- Datum für den 1. bis 4. Adventssonntag
Das Jahr für die Berechnung steht in Zelle A30.
Für die Freaks wird das wahrscheinlich kein Problem sein, ich bekomme es aber nicht hin.
Vielen Dank für Eure Hilfe.
Gruß
Ralf
AW: Formel für variable Datumsberechnung gesucht
18.06.2010 07:09:49
Hajo_Zi
Hallo Ralf,
die Feiertage.
Frei
 ABCD
1Jahr2008   
2  Feiertag im
Bundesland		Text der geschrieben
wird		 
301.01.200801.01.2008FeiertagNeujahr
406.01.2008    hl. 3 Könige
504.02.2008    Rosenmontag
621.03.200821.03.2008FeiertagKarfreitag
722.03.2008    Ostersamstag
823.03.200823.03.2008FeiertagOstersonntag
924.03.200824.03.2008FeiertagOstermontag
1001.05.200801.05.2008FeiertagMaifeiertag
1101.05.200801.05.2008FeiertagChristi Himmelfahrt
1211.05.2008    Muttertag Formel von M.I.Nitraum
1310.05.2008    Pfingstsamstag
1411.05.200811.05.2008FeiertagPfingstsonntag
1512.05.200812.05.2008FeiertagPfingstmontag
1622.05.2008    Fronleichnam
1703.10.200803.10.2008FeiertagNationalfeiertag
1805.10.2008    laut Hinweis im Forum gibt es noch unterschiede in den Konfesion (evangelich / katolisch)
1931.10.200831.10.2008FeiertagReformationstag
2001.11.2008    Allerheiligen
2116.11.2008    Volkstrauertag
2219.11.2008    Buss- und Betag
2323.11.2008    Totensonntag/Ewigkeitssontag
2430.11.2008    1. Advent
2507.12.2008    2. Advent
2614.12.2008    3. Advent
2721.12.2008    4. Advent
2824.12.2008    hl. Abend
2925.12.200825.12.2008Feiertag1. Weihnachtstag
3026.12.200826.12.2008Feiertag2. Weihnachtstag
3131.12.2008    Silvester
3230.03.2008    Zeitumstellung letzten Sonntag im März um 01:00 Weltzeit (02:00 bei uns - auf 03:00)
3326.10.2008    Zeitumstellung letzten Sonntag im Oktober um 01:00 Weltzeit

verwendete Formeln
Zelle Formel Bereich
B1 =JAHR('01'!B1) 
A3 =DATWERT("01.01."&B1) 
B3 =A3 
A4 =DATWERT("06.01."&B1) 
A5 =A8-48 
A6 =A8-2 
B6 =A6 
A7 =A8-1 
A8 =DM((TAG(MINUTE(B1/38)/2+55) & ".4." & B1)/7;)*7-WENN(JAHR(1)=1904;5;6) 
B8 =A8 
A9 =A8+1 
B9 =A9 
A10 =DATWERT("01.05."&B1) 
B10 =A10 
A11 =A8+39 
B11 =A11 
A12 =DATUM(B1;5;1)+15-WOCHENTAG(DATUM(B1;5;1)) 
A13 =A8+48 
A14 =A8+49 
B14 =A14 
A15 =A8+50 
B15 =A15 
A16 =A8+60 
A17 =DATWERT("03.10."&B1) 
B17 =A17 
A18 =DATUM(B1;10;1)+7-WOCHENTAG(DATUM(B1;10;1);2) 
A19 =DATWERT("31.10."&B1) 
B19 =A19 
A20 =DATWERT("01.11."&B1) 
A21 =DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-35 
A22 =DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-32 
A23 =DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-28 
A24 =DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-21 
A25 =DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-14 
A26 =DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-7 
A27 =DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2) 
A28 =DATWERT("24.12."&B1) 
A29 =DATWERT("25.12."&B1) 
B29 =A29 
A30 =DATWERT("26.12."&B1) 
B30 =A30 
A31 =DATWERT("31.12."&B1) 
A32 =DATUM(B1;3;31)-REST(WOCHENTAG(DATUM(B1;3;31);2);7) 
A33 =(DATUM(B1;10;1))-(WOCHENTAG((DATUM(B1;10;31));2))+30 

definierte Namen
Name Bezieht sich auf Tabelle Z1S1-Formel
Feiertage=Frei!$B$2:$C33 =Frei!Z2S2:Z(32)S3

Tabellendarstellung in Foren Version 5.28



Anzeige
AW: Formel für variable Datumsberechnung gesucht
18.06.2010 07:30:30
Ralf
Hallo Hajo,
super.
Vielen Dank.
Eine Angabe fehlt mir jedoch noch: Wie komme ich an das Datum des 3. Samstages im September?
Gruß
Ralf
3. Samstag im September
18.06.2010 08:02:03
WF
Hi,
folgende Arrayformel:
{=DATUM(A30;9;KKLEINSTE(WENN(WOCHENTAG(DATUM(A30;9;ZEILE(1:30));2)=6;ZEILE(1:30));3))}
Salut WF
Dritter Samstag im September
18.06.2010 08:46:39
Erich
Hi Ralf,
wenn in B1 das Jahr steht, einfach so - ohne Matrixformel:
=(9&-B1)+6-REST((9&-B1)-1;7)+21
Allgemeiner findest du das hier:
www.online-excel.de/fom/fo_read.php?f=1&bzh=49139&h=49114
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: dann aber +14
18.06.2010 09:28:22
Ralf
Hallo Erich und der Rest,
passt.
Vielen Dank für Eure Hilfe.
Viele Grüße und schönes Wochenende
Ralf
dann aber +20
18.06.2010 10:02:33
Erich
Hi Walter,
stimmt, +21 war eine Woche zu viel - daher:
=(9&-B$1)-REST((9&-B$1)-1;7)+20
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: dann aber +20
18.06.2010 10:10:36
Ralf
Passt.
Feiertagstabelle
18.06.2010 08:54:43
Erich
Hi Ralf,
hier mein Vorschlag für die Feiertage:
 ABC
1Jahr:2010Bereich FTage
2   
3DatumregionalFeiertag
4Fr  01.01.201001.01.10Neujahr
5Mi  06.01.2010 Dreikönigstag
6Mo  15.02.2010 Rosenmontag
7Di  16.02.2010 Fastnacht
8Mi  17.02.2010 Aschermittw.
9Fr  02.04.201002.04.10Karfreitag
10So  04.04.2010 Ostersonntag
11Mo  05.04.201005.04.10Ostermontag
12Sa  01.05.201001.05.10Tag d. Arbeit
13Do  13.05.201013.05.10Himmelfahrt
14So  23.05.2010 Pfingstsonntag
15Mo  24.05.201024.05.10Pfingstmontag
16Do  03.06.201003.06.10Fronleichnam
17So  08.08.2010 Friedensfest
18So  15.08.2010 Mariä Himmelfahrt
19So  03.10.201003.10.10Tag der d. Einheit
20So  03.10.2010 Erntedankfest
21So  31.10.2010 Reformationstag
22Mo  01.11.201001.11.10Allerheiligen
23Mi  17.11.2010 Buß- und Bettag
24So  21.11.2010 Totensonntag
25So  28.11.2010 1. Advent
26So  05.12.2010 2. Advent
27So  12.12.2010 3. Advent
28So  19.12.2010 4. Advent
29Fr  24.12.2010 Heiligabend
30Sa  25.12.201025.12.101. Weihnachtstag
31So  26.12.201026.12.102. Weihnachtstag
32Fr  31.12.2010 Silvester

Formeln der Tabelle
ZelleFormel
A4=(1&-B1)+0
B4=A4
A5=A4+5
A6=A10-48
A7=A6+1
A8=A7+1
A9=A10-2
B9=A9
A10=7*DM((4&-B1)/7+REST(19*REST(B1;19)-7;30)*14%;)-6
A11=A10+1
B11=A11
A12=(5&-B1)+0
B12=A12
A13=A10+39
B13=A13
A14=A13+10
A15=A14+1
B15=A15
A16=A14+11
B16=A16
A17=(8&-B1)+7
A18=A17+7
A19=(10&-B1)+2
B19=A19
A20=A19-REST(A19+3;7)+4
A21=(11&-B1)-1
A22=A21+1
B22=A22
A23=A24-4
A24=A25-7
A25=A26-7
A26=A27-7
A27=A28-7
A28=A29-REST(A30-2;7)
A29=(12&-B1)+23
A30=A29+1
B30=A30
A31=A30+1
B31=A31
A32=A31+5

Die Osterformel ist von Frank: http://www.online-excel.de/fom/fo_read.php?f=2&bzh=-1&h=812&ao=1
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Feiertagstabelle
18.06.2010 11:00:20
Wilfried
Hallo!
Wie müsste die Formel lauten wenn ich einen anderen Tag haben will?
zB. 3ter Mittwoch von Monat 9
Danke für eine Antwort.
Gruß
Wilfried
und der dritte Mittwoch
18.06.2010 11:29:13
Erich
Hi Wilfried,
für andere Wochentage geht das so:
 BCD
120105Mi   15.09.10
2  Mi   15.09.10
3 0Freitag
4 1Samstag
5 2Sonntag
6 3Montag
7 4Dienstag
8 5Mittwoch
9 6Donnerstag

Formeln der Tabelle
ZelleFormel
D1=(9&-B1)-REST((9&-B1)-C1;7)+20
D2=(9&-B1)-REST((9&-B1)-5;7)+20

Die 5 steht da für Mittwoch.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: und der dritte Mittwoch
18.06.2010 12:07:35
Wilfried
Hallo Erich!
Danke für die Formel. Passt genau.
Gruß
Wilfried
noch nen Datum
21.06.2010 11:22:52
Ralf
Hallo Forum,
eine Berechnungsformel suche ich noch für den Kalender:
Die Kieler Woche findet jedes Jahr in der letzten vollen Juni Woche statt.
2010 also vom 19.06. - 27.06.2010
Wie kann ich Beginn und Ende als variables Datum in einer Formel darstellen?
Danke im voraus für Eure Hilfe.
Gruß
Ralf
Termin der Kieler Woche
21.06.2010 11:52:55
Erich
Hi Ralf,
wenn in B1 das Jahr steht:
 ABC
18Sa  19.06.2010 Kieler Woche Start
19So  27.06.2010 Kieler Woche Ende

Formeln der Tabelle
ZelleFormel
A18=A19-8
A19=(7&-B1)-1-REST((7&-B1)-2;7)

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Termin der Kieler Woche
21.06.2010 13:47:56
Ralf
Hallo Erich,
passt.
Danke für Deine Hilfe.
Gruß aus München
Ralf
AW: Termin der Kieler Woche
21.06.2010 16:18:37
Ralf
Hallo Erich,
jetzt ist mir doch noch ein Datum eigefallen:
Das Ende des Oktoberfestes ist der 1. Sonntag im Oktober.
Ich habe versucht die Kieler Woche Formel anzupassen, leider ohne Erfolg.
Vielleicht kannst Du mir nochmal aushelfen. Welche Systematik stegt dahinter? Dann verstehe ich es vielleicht selber mal.
Danke im voraus für Deine Hilfe.
Gruß
Ralf
Termin Oktoberfest
21.06.2010 18:13:27
Erich
Hi Ralf,
warum eigentlich lässt du im Betreff "Kieler Woche" stehen, wo du doch nach dem Oktoberfest fragst?
Der Betreff ist oft Suchkriterium...
Das Datum des 1. Sonntags im Okt. findest du in meiner Feiertagstabelle (in diesem Thread)
als Erntedankfest.
Das Oktoberfest endet nicht unbedingt an diesem Sonntag - es geht oft bis zum 3. Okt.
Schau mal bei Wiki zu Oktoberfest, dort unter "Termine/Öffnungszeiten".
Hilfreich zum Verstehen sind vielleicht diese Seiten:
http://www.online-excel.de/excel/singsel.php?f=128
http://www.online-excel.de/fom/fo_read.php?f=1&bzh=49139&h=49114
http://www.excelformeln.de/formeln.html?welcher=187
http://www.excelformeln.de/formeln.html?welcher=372
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Termin Oktoberfest
22.06.2010 06:51:07
Ralf
Hallo Erich,
das Oktoberfest endet nur am 3.10. wenn,
- der 3.10. am Sonntag ist
- der 3.10 am Montag ist
Schönen Gruß aus München
Ralf
Termin Oktoberfest
22.06.2010 07:35:41
Erich
Hi Ralf,
die Formel dazu habe ich dir doch gestern schon im Thread
https://www.herber.de/forum/messages/1163275.html
geschrieben, direkt danach auch eine Vereinfachung für das Oktoberfest-Ende.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Termin Oktoberfest
22.06.2010 08:15:52
Ralf
Hallo Erich,
ja, ich habe Sie heute Morgen in meine Tabelle kopiert. Meine Antwort war lediglich eine Ergänzung zu Deiner Aussage.
Ich werde mir Deine Beschreibungen jetzt mal durchlesen, um die Systematik zu verstehen.
Danke für Deine Hilfe.
Gruß
Ralf
Anzeige
Feiertagstabelle Nr. 2
21.06.2010 19:48:47
Erich
Hi Ralf,
hier noch mal alle Feiertage, mit teils vereinfachten Formeln:
 ABCD
1Jahr:2009Bereich FTage
(unsortiert)		 
2    
3DatumregionalFeiertag 
4Do  01.01.200901.01.09Neujahr 
5Di  06.01.2009 Dreikönigstag 
6Mo  23.02.2009 Rosenmontag 
7Di  24.02.2009 Fastnacht 
8Mi  25.02.2009 Aschermittw. 
9Fr  10.04.200910.04.09Karfreitag 
10So  12.04.2009 Ostersonntag 
11Mo  13.04.200913.04.09Ostermontag 
12Fr  01.05.200901.05.09Tag d. Arbeit 
13Do  21.05.200921.05.09Himmelfahrt 
14So  31.05.2009 Pfingstsonntag 
15Mo  01.06.200901.06.09Pfingstmontag 
16Do  11.06.200911.06.09Fronleichnam 
17Sa  20.06.2009 Kieler Woche Start 
18So  28.06.2009 Kieler Woche Endeletzter Sonntag im Juni
19Sa  08.08.2009 Friedensfest 
20Sa  15.08.2009 Mariä Himmelfahrt 
21Sa  19.09.2009 Oktoberfest StartSamstag nach 15.09.
22Sa  03.10.200903.10.09Tag der d. Einheit 
23So  04.10.2009 Oktoberfest Ende1.So im Okt oder 3.Okt
24So  04.10.2009 Erntedankfest1.So im Okt
25Sa  31.10.2009 Reformationstag 
26So  01.11.200901.11.09Allerheiligen 
27Mi  18.11.2009 Buß- und Bettag 
28So  22.11.2009 Totensonntag 
29So  29.11.2009 1. Advent 
30So  06.12.2009 2. Advent 
31So  13.12.2009 3. Advent 
32So  20.12.2009 4. Advent 
33Fr  25.12.2009 Heiligabend 
34Sa  26.12.200926.12.091. Weihnachtstag 
35So  27.12.200927.12.092. Weihnachtstag 
36Fr  01.01.2010 Silvester 

Formeln der Tabelle
ZelleFormel
A4=(1&-B1)+0
B4=A4
A5=A4+5
A6=A10-48
A7=A6+1
A8=A7+1
A9=A10-2
B9=A9
A10=7*DM((4&-B1)/7+REST(19*REST(B1;19)-7;30)*14%;)-6
A11=A10+1
B11=A11
A12=(5&-B1)+0
B12=A12
A13=A10+39
B13=A13
A14=A13+10
A15=A14+1
B15=A15
A16=A15+10
B16=A16
A17=A18-8
A18=A12+60-REST(A12+59;7)
A19=A12+99
A20=A19+7
A21=A19+45-REST(A19+38;7)
A22=A12+155
B22=A22
A23=A21+15+MAX(;3-TAG(A21+15))
A24=A22-REST(A22+3;7)+4
A25=A22+28
A26=A25+1
B26=A26
A27=A28-4
A28=A29-7
A29=A30-7
A30=A31-7
A31=A32-7
A32=A33-REST(A34-2;7)
A33=A26+54
A34=A33+1
B34=A34
A35=A34+1
B35=A35
A36=A35+5

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Feiertagstabelle Nr. 2 - Oktoberfest-Ende
21.06.2010 19:53:38
Erich
Hi,
das Oktoberfest-Ende geht viel einfacher so:
=MAX(A21+15;A22)
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

302 Forumthreads zu ähnlichen Themen

Automatische Werte befüllen wenn bestimmtes Datum
Heiderer

Hallo!
Ich habe folgendes Excel-Sheet:
In Spalte A soll mittels VBA ein Datum eingefügt werden, zb. 01.09.2008. In Spalte B soll ein Betrag zb. € 70,00 eingetragen werden. Bezüglich der Spalte B gibt es kein Problem. Es geht nur um die Spalte A.
Ich will nämlich, dass Excel autom...
03.09.2008 13:43:00
Thread-ID: 1007096
30 Beiträge
Anzeige
Wenn ijn Spalte Datum von Jänner dann Werte zählen
Jänner

Hallo!
Ich habe in der Spalte A ab der Zeile 3 Datumwerte aus den Wochenenden und den Feiertagen stehen.
In der spalte G, H, I stehen nun Werte.
Ich möchte nun alle Werte in Abhängigkeit des Datums zusammenzählen.
Ist in der Spalte A das Jänner Datum vorhanden dann sollen die We...
07.01.2009 08:42:00
Thread-ID: 1037440
4 Beiträge
Datum in Monaten (mit 0,5) - ohne VBA
Wolfgang

Hallo,
ich bin auf der Suche nach einer Formel (nicht VBA), welche die Differenz zwischen Datum in Spalte F und Spalte G (z.B. 12.01.09 - 28.02.09) ermittelt und mir als Monat mit einer Stelle nach dem Komma z.B. 1,5 auswirft. Danke schon jetzt für die Rückmeldungen.
Herzliche Grüße
...
20.01.2009 14:30:25
Thread-ID: 1041840
5 Beiträge
Anzeige
Wenn Dann mit Werten zwischen 2 Zellen
Thomas

Hallo,
montagsgemäß steh ich gerade auf dem Schlauch und benötoge Hilfe für folgendes Problem:
ich habe mind. 10 Werte in % von 50% bis 110%
In den Zellen A1 bis A10 steht ein Text, dahinter also in B1 bis B10 die Prozentwerte.
Dann steht in C1 ein Objektspezifischer Wert/ein E...
23.02.2009 11:55:52
Thread-ID: 1053025
3 Beiträge
Ausgangsdatum + X Monate
Bertram

Hallo zusammen,
ich stehe heute mal wieder voll auf dem Schlauch, daher sollte mein Level lauten "Excel warum-einfach-wenn's-kompliziert-geht"
Ich möchte von einem Ausgangsdatum 3,6,9 und 12 Monate addieren und diese Monate Xen. Mit folgender Formel geht das. Aber geht das auch kürzer?<...
06.03.2009 10:39:42
Thread-ID: 1057192
7 Beiträge
Mehr als sieben Werte bei WENN(Prüfung;Dann_Wert;S
Florian

Hallo zusammen,
WENN(Prüfung;Dann_Wert;Sonst_Wert). Leider kann ich nicht mehr als sieben Werte eingeben.
Gibt es eine Möglichkeit diese Obergrenze zu Umgehen?
Danke schon mal und schönes Wochenende :-)
13.03.2009 14:55:58
Thread-ID: 1059900
4 Beiträge
wenn datum, dann datum
Jan

hallo zusammen
ich habe eine kleine frage..
ich möchte, das wenn in B2 ein Datum steht (z.b. 31.01.2009), dass dann Excel in A2 dieses Datum schreibt, ansonsten soll es nichts eintragen. bin nicht so der excel-profi und wäre für jede hilfe dankbar!
mir fällt momentan nur das ein:...
16.03.2009 15:10:12
Thread-ID: 1060776
3 Beiträge
Monat und Jahr aus Datum extrahieren
ItsMyLife

Hallo,
für ein Diagramm will ich die Anzahl von Datensätzen die nach dem Datumsfeld in einen Monat fallen ermitteln.
Wie kann ich den Monat und das Jahr aus einem Datumsfeld extrahieren.
Ich meine nicht die Anzeige (Format Zellen), sondern den tatsächlichen Feldinhalt?
19.05.2009 14:21:13
Thread-ID: 1076146
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
29
6
3
4
8
2
6
3
2
2
8
1
2
5
11
4
3
3
9
7
11
21
1
2
2