Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1704to1708
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
Inhaltsverzeichnis

Zeiträume berechnen - Datum von und bis

Zeiträume berechnen - Datum von und bis
09.08.2019 14:21:30
und
Hallo zusammen
Ich komme bei meinem Problem einfach nicht weiter...
Ich muss Zeiträume zwischen Datumsangaben berechnen.
Klingt einfach
Beispiele manuell errechnet:
01.01.19 – 31.01.19 = 30 Tage
01.01.19 – 30.01.19 = auch 30 Tage
15.01.19 – 31.01.19 = 16 Tage
15.01.19 - 30.01.19 = auch 16 Tage
01.02.19 – 28.02.19 = 30 Tage
01.03.19 – 15.03.19 = 15 Tage
Alle Monate werden also mit 30 Tagen berechnet.
Ich weiß das es die Sache "Tage360" gibt.
Aber das klappt in meinem/unserem Fall nicht.
Habe ich z. B. den Zeitraum 02.01.-28.02. (oder 29.02.) müssten 59 Tage herauskommen.
Mit der Formel "TAGE360(A1;A2)" schmeißt es mir 56 Tage aus.
Für den Januar müssten aber in dem Beispiel 29 Tage und für den Februar 30 Tage gezählt werden - also 59 gesamt.
Dass Ganze hat mit der Berechnung von Dienstzeiten zu tun.
Und natürlich (wie soll es auch anders sein) kann es vorkommen, dass der Zeitraum über mehrere Jahre geht.
Ich habe wirklich viel im Netz gesucht (und auch gefunden).
Aber unser Problem konnte ich einfach nicht lösen.
Vielleicht kann mir hier jemand helfen?
Eine VBA-Lösung wäre natürlich auch ok für mich. ;-)
Gruß und ein schönes Wochenende
Werner

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ein erster schneller Vorschlag ...
09.08.2019 15:34:27
neopa
Hallo Namensvetter,
... der lediglich zusammengestellt und auch nur oberflächlich geprüft ist. Aus diesem Grund auch keine optimale Formel darstellt. Daten stehen in Spalte A und B. Am problematischsten sind natürlich Februardaten.
=(TAG(B1)&ltTAG(A1))*(MIN(TAG(MONATSENDE(A1;0));30)-TAG(A1)+TAG(B1))*(DATEDIF(A1;B1+1;"M")&gt0)+DATEDIF(A1;B1+1;"M")*30+(TAG(B1)&gt=TAG(A1))*(DATEDIF(A1;B1+1;"M")=0)*(MIN(TAG(B1);30)-TAG(A1)+1)*(A1&gt0)+(DATEDIF(A1;B1;"M")&gt0)*(TAG(B1)&gt=TAG(A1))*(TAG(B1)-TAG(A1)+1)
Gruß Werner
.. , - ...
AW: Zeiträume berechnen - Datum von und bis
09.08.2019 16:30:46
und
Hi
meine Variante.
Februar wird mit 30 Tagen berücksichtigt, wenn er als ganzer Monat vorliegt (Datum liegt auf dem Monatsletzten, dh 28 oder 29 Februar):
hat natürlich den Effekt, dass ein Bereich: 1.2 -27.2 mit 27 Tagen und ein Bereich 1.2-28.2 mit 30 Tagen gezählt wird.
=WENN(TEXT(A1;"JJJJMM")=TEXT(A2;"JJJJMM");WENN(A2=MONATSENDE(A2;0);30;TAG(A2))-TAG(A1)+1; DATEDIF(DATUM(JAHR(A1);MONAT(A1)+1;1);DATUM(JAHR(A2);MONAT(A2);1);"m")*30+WENN(A2=MONATSENDE(A2;0); 30;TAG(A2))+31-TAG(A1))
hier die Version für den Fall, dass deine Excelversion die Funktion "Monatsende" nicht kennt:
=WENN(TEXT(A1;"JJJJMM")=TEXT(A2;"JJJJMM");WENN(A2=DATUM(JAHR(A2);MONAT(A2)+1;0);30;TAG(A2))-TAG(A1) +1;DATEDIF(DATUM(JAHR(A1);MONAT(A1)+1;1);DATUM(JAHR(A2);MONAT(A2);1);"m")*30+WENN(A2=DATUM(JAHR(A2); MONAT(A2)+1;0);30;TAG(A2))+31-TAG(A1))
Gruß Daniel
Anzeige
AW: Zeiträume berechnen - Datum von und bis
09.08.2019 18:59:51
und
Hallo Werner,
Ich habe gerade keinen Zugriff auf Excel, aber wie wäre es mit
Tage360(A1-1;A2)
oder
Tage360(A1;A2+1)
?
Viele Grüße,
Bernd P
AW: bisherige Lösungsansatze ...
10.08.2019 09:18:05
neopa
Hallo Werner,
... jedoch musste eben feststellen, dass ich gestern eine nicht ganz korrekte "Vorgängerformel" eingestellt hatte. Nachfolgend ist in E2 meine korrigierte eingestellt, die jedoch noch immer nicht optimiert ist, weil für mich noch nicht geklärt ist, ob deren Ergebnisse auch Deinen Vorstellungen entspricht.
Die Formeln von Daniel und die 2. Variante von Bernd liefern die gleiche Ergebnisse sind aber mE nicht immer Deinem Wunsch entsprechend, siehe F14:G15. Oder doch?
Wenn meine Formelergebnisse für Dich die richtigen sein sollten, dann könnte man diese noch umschreiben auf eine analoge kürzere Variante unter Nutzung von TAGE360()
Arbeitsblatt mit dem Namen 'F_190809_2'
 ABCDEFG
1BeginnEnde  neopaDanielBernd
201.01.201931.01.2019  303030
301.01.201930.01.2019  303030
415.01.201931.01.2019  161616
515.01.201930.01.2019  161616
601.03.201915.03.2019  151515
702.01.201928.02.2019  595959
802.01.201628.02.2016  575757
902.02.201628.02.2016  272727
1002.02.201602.03.2016  313131
1101.02.201629.02.2016  303030
1201.02.201628.02.2016  282828
1301.02.201928.03.2019  585858
1405.12.201803.04.2019  118119119
1502.02.201629.02.2016  282929
16    011

ZelleFormel
E2=(TAG(B2)&ltTAG(A2))*(MIN(TAG(MONATSENDE(A2;0));30)-TAG(A2)+TAG(B2))*(DATEDIF(A2;B2+1;"M")&gt0)+DATEDIF(A2;B2+1;"M")*30+(TAG(B2)&gt=TAG(A2))*(DATEDIF(A2;B2+1;"M")*(MIN(TAG(B2);30)-TAG(A2)+1)*(A2&gt0)+(DATEDIF(A2;B2;"M")&gt0)*(TAG(B2)>=TAG(A2))*(WENN(TAG(B2)=TAG(MONATSENDE(B2;0));30;TAG(B2))-TAG(A2)+1)
F2=WENN(TEXT(A2;"JJJJMM")=TEXT(B2;"JJJJMM");WENN(B2=MONATSENDE(B2;0);30;TAG(B2))-TAG(A2)+1; DATEDIF(DATUM(JAHR(A2);MONAT(A2)+1;1);DATUM(JAHR(B2);MONAT(B2);1);"m")*30+WENN(B2=MONATSENDE(B2;0); 30;TAG(B2))+31-TAG(A2))
G2=TAGE360(A2;B2+1)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: bisherige Lösungsansatze ...
10.08.2019 14:20:39
Daniel
Hi Werner
wobei ich der Meinung bin, dass 119 als Ergebnis für den Zeitraum: 05.12.2018-03.04.2019
das korrekte Ergebnis sein muss.
das ganze setzt sich ja so zusammen (ich fange mal mit den eindeutigen Werten an)
+ 3 Tage im April
+ 3 ganze Monate (Jan, Feb, Mrz) = 3*30 = 90 Tage
+ 26 Tage im Dezember, den wenn es vom 1. bis 30. (=Monatsletzten) 30 Tage sein sollen (Zeile 2 und 3), dann sind es vom 5. bis zum 30. 26 Tage (oder vergleiche auch Zeile 5: 15. bis 30. = 16 Tage):
macht: 3+30+26 = 119
was den Februar angeht (Zeile 9 und 15):
hier ist es eine Definitionsfrage, wann genau der Februar mit 30 Tagen gezählt werden soll:
ich habe mich dafür entschieden, auch um keine zusätzlichen Sonderfälle aufkommen zu lassen, das Enddatum immer auf den 30. zu setzten, wenn es auf das Ende eines Monats fällt (und das kann beim Februar mal der 28. und mal der 29. sein)
Gruß Daniel
Anzeige
AW: teils, teils ... oder gänzlich anders ...
10.08.2019 16:17:08
neopa
Hallo Daniel,
... hzgl. dem Ergebnis in Zeile 14 meiner Gegenüberstellung gebe ich Dir Recht. Da hab ich offensichtlich den jeweils ersten Tag "unterschlagen". Dies kann ich in meiner Formel ganz einfach durch die Addition einer 1 im ersten Teil meiner Formel korrigieren (immer noch ohne die Formel bzgl. Länge zu optimieren, da ich jetzt dafür eh eine Kombination von TAGE360() mit Zusatzbedingungen favorisieren würde. wenn der Fragesteller sich dafür entscheiden sollte).
Also in meiner zuletzt eingestellten Formel so:
=(TAG(B2)&ltTAG(A2))*(MIN(TAG(MONATSENDE(A2;0));30)-TAG(A2)+TAG(B2)+1)*(...
Es bleibt die Problematik über das den Februarmonatsende hinaus, wenn der der Beginn im Februar und nicht am 1.2. liegt. Z.B. ermittele ich vom 25.2.19 bis 3.04.19 mE richtig 37 Tage, ihr jedoch 39 Tage.
Wenn letzteres richtig sein soll, dann reicht der Vorschlag von Bernd mit =TAGE360(A2;B2+1) aus und dann bedarf es weder meiner noch Deiner Formel.
Gruß Werner
.. , - ...
Anzeige
AW: teils, teils ... oder gänzlich anders ...
10.08.2019 17:03:58
Josef
Hallo Werner
Ich denke auch, dass der Fragesteller uns genau sagen sollte, wie er die Tage für den Februar zählen will.
Der Vorschlag von Bernd wird es aber eher nicht sein.
30.04.2019 bis 30.05.2019 ergibt bei seiner Formel 30 Tage statt 31
Bei: http://www.excelformeln.de/formeln.html?welcher=341
wird dieses Thema auch behandelt.
Eventuel hilft da meine etwas modifizierte kurze Variante
=TAGE360(A1;B1+(TAG(B1+1)=1))+(TAG(B1+1)>1)
Dort ist auch zusätzlich für den Februar eine andere Berechnungsvariante vorhanden.
Bei der Formel von dir Werner, wurde leider irgendwo eine Klammer "gefressen"
Ein schönes Wochenende wünscht
Sepp
Anzeige
AW: zu Deinen Feststellungen ...
10.08.2019 19:30:15
neopa
Hallo Sepp,
... die "gefressene" Klammer in meiner Formel ist offensichtlich dem notwendigen Korrekturen "&gt" sowie"&lt"-Zeichen des HRML-Codes, welcher von Gerd Bambergs Code erzeugt wird und von der hiesigen Forumssoftware ansonsten nicht akzeptiert wird, geschuldet.
Ich stell meine Formel deshalb für E2 korrigiert noch einmal ein:
=(TAG(B2)&ltTAG(A2))*(MIN(TAG(MONATSENDE(A2;0));30)-TAG(A2)+TAG(B2)+1)*(DATEDIF(A2;B2+1;"M")&gt0)
+DATEDIF(A2;B2+1;"M")*30+(TAG(B2)&gt=TAG(A2))*(DATEDIF(A2;B2+1;"M")=0)*(MIN(TAG(B2);30)-TAG(A2)+1)*
(A2&gt0)+(DATEDIF(A2;B2;"M")&gt0)*(TAG(B2)&gt=TAG(A2))*(WENN(TAG(B2)=TAG(MONATSENDE(B2;0));30;TAG(B2))-
TAG(A2)+1)

Im Weiteren:
Mit Deiner Kurzformel ergeben sich die gleichen Ergebnisse wie mit Daniels Formel und somit für den 02.2.19 bis 28.02.19 auch 29 Tage, während meine Formel 27 Tage ermittelt. Für mich wäre letzteres Ergebnis verständlicher. Aber wenn es eine andere Festlegung geben sollte, dann ist ist halt so.
Wünsche Dir auch ein schönes WE
Gruß Werner
.. , - ...
Anzeige
AW: Zeiträume = Zinstage?
10.08.2019 22:16:06
Niclaus
Grüezi
Die 30/360-Formel wird ja vor allem für die Zinsberechnung verwendet – heutzutage zwar immer weniger. Werner Brauns müsste also sagen, wofür er die Berechnung der Zeiträume braucht.
In meiner kaufmännischen Ausbildung anfangs der 1960er Jahre wurde darauf hingewiesen, dass fast jedes Land eine eigene Definition für die 30/360-Methode habe. Darauf weist auch der aktuelle Wikipedia-Artikel "Zinsberechnungsmethode" hin.
Für die Schweizer Usanz gilt nach wie vor, was im "Schweizerischen Lehrbuch für Kaufleute", 2. Auflage, Winterthur 1893 festgehalten ist: "Als unveränderliche Regel gilt: Der Tag, an welchem Jemand ein Capital empfängt, darf bei Berechnung der Zinsen nicht mitgezählt werden, so dass also ein Capital am 1. Januar ausgeliehen und am 10. Januar heimbezahlt, nur 9 Tage Zinsen trägt."
Nach der Schweizer Usanz sind
01.01.19 – 31.01.19 = 29 Zinstage
01.01.19 – 30.01.19 = 29 Zinstage
Diese Schweizer Usanz ist in der folgenden Function festgehalten:
Function tagewr(zahl1, zahl2)   ' Zinsberechnungmethode 30/360 nach Schweizer Usanz
r1 = Year(zahl1) Mod 4          ' Gilt auch für die Schaltjahre.
r2 = Year(zahl2) Mod 4          ' Gilt aber nicht für die Jahre 1900, 2100, 2200 usw.,
' die ja keine Schaltjahre sind.
If r1 = 0 And Month(zahl1) = 2 And Day(zahl1) = 29 Then
f1 = 1
ElseIf r1 > 0 And Month(zahl1) = 2 And Day(zahl1) = 28 Then
f1 = 2
Else: f1 = 0
End If
If r2 = 0 And Month(zahl2) = 2 And Day(zahl2) = 29 Then
f2 = 1
ElseIf r2 > 0 And Month(zahl2) = 2 And Day(zahl2) = 28 Then
f2 = 2
Else: f2 = 0
End If
tagewr = Application.Days360(zahl1, zahl2, 1) - f1 + f2
End Function

Grüsse Niclaus
Anzeige
AW: danke für Deine Information ...
11.08.2019 09:17:38
neopa
Hallo Niclaus,
... wir können jetzt nur abwarten, für was der TE bzw. zumindest wie er die Auswertung wirklich benötigt.
Gruß Werner
.. , - ...
AW: Zeiträume berechnen - Datum von und bis
12.08.2019 21:53:48
und
Hallo Werner,
Um welchen Staat, welches Bundesland und welche Berufsgruppe handelt es sich hier?
Ohne Hinweis auf die rechtliche Grundlage für die Dienstzeiten ist Alles nur Raten und Irren, fürchte ich.
Viele Grüße,
Bernd P
AW: Zeiträume berechnen - Datum von und bis
14.08.2019 09:52:52
und
Hallo zusammen!
Ich hatte die größten Probleme auf den Thread zu antworten.
Nicht das ihr denkt "Ihm wurde geholfen und jetzt sagt er nicht mal Danke".
DANKE!!!
Offenbar hatte ich die falsche URL und habe das nicht gemerkt.
Hans hat mir dann geholfen - Danke Hans!
Ich habe mich mit meiner Kollegin unterhalten - wir nehmen die TAGE360-Formel und wir addieren 1 Tag.
Sie konnte mir noch nicht mal sagen, wie sie aktuell mit dem Februar umgehen:
01.-28.02. = 30 Tage
01.-27.02. = 27 Tage
Auch das nehmen wir jetzt so - wie auch sonst.
Gebraucht wird das Ganze zur Berechnung von Dienstzeiten.
Fängt jemand bei uns an wird geprüft, ob derjenige eine Zeit hatte, die auf die Dienstzeit angerechnet werden kann.
Beispiel:
Einstellung bei uns zum 01.10.19
Zeit bei der Bundeswehr vom 01.01.17 bis 31.12.18 (2 X 360 Tage = 720 Tage).
Dienstzeitbeginn bei uns für denjenigen wäre damit 01.10.17
Ich danke nochmals allen die geholfen haben!
Klasse das es das Forum gibt!
Gruß
Werner
Anzeige

303 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige