AW: Perioden ausrechnen (Datum)
04.07.2022 16:05:12
Daniel
Hi
so jetzt nochmal eine reine Excelformellösung
ich teil das mal über mehrere Spalten auf, damit das Formelwerk übersichtlich bleibt.
Man kann aber auf die zusätzlichen Spalten verzichten, wenn man die Formeln aus den Hilfsspalten in die Hauptformel einsetzt.
Bei Excel 365 auch gerne mit Hilfe von LET, da einige Berechnungen mehrfach verwendet werden. Da ich kein 365 habe, hier das Formelwerk wie es ab Excel 2013 funktionieren würde:
A1: die Perioden, so wie angegeben. Anzahl beliebig.
B1: Anzahl der einzelnen Datumswerte berechnen:
=(Länge(A1)-Länge(Wechseln(A1;"-";""))*2
C1: Text aus A1 so umbauen, dass er mit XMLFORMAT gesplittet werden kann:
=""&WECHSELN(WECHSELN(A1;"-";";");";";"")&""
D1: Summe der AB-Datumswerte
=SUMMENPRODUKT(XMLFILTERN(B4;"//x/y")*ISTUNGERADE(ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;B1;1))))
E1: Summe der BIS-Datumswerte
=SUMMENPRODUKT(XMLFILTERN(B4;"//x/y")*ISTGERADE(ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;B1;1))))
F1: Summe der Differenzen:
=E1-F1-B1/2
wie gesagt, das lässt sich auch zu einer einzigen Formel zusammenfassen, aber das überlasse ich jedem selbst als Fleißaufgabe.
in Excel 365 lässt sich das mit LET und SEQUENZ noch sicherlich vereinfachen.
Sollte es das AUSWERTEN aus den Excel4Makros wieder in die normalen Excelfunktionen schaffen, kann man natürlich auch die von mir zuerst gezeigte Methode verwenden.
als VBA würde das ganze natürlich ganz einfach gehen, hier der Code für die entsprechende Funktion, die dann verwendet werden kann:
dh folgender Code in einem allgemeinen Modul
Function AnzahlTage(Perioden As String, Optional AngebrocheneTageZählen As Long = 0)
Dim arrDatum
Dim i As Long
Perioden = Replace(Perioden, "-", ";")
arrDatum = Split(Perioden, ";")
For i = 1 To UBound(arrDatum) Step 2
AnzahlTage = AnzahlTage + CDate(arrDatum(i)) - CDate(arrDatum(i - 1)) + AngebrocheneTageZählen
Next
End Function
und dann in A2
=AnzahlTage(A1;-1)
der zweite Parameter gibt hier an, wie mit den angebrochenen Tagen umgegangen werden soll (ab- und bis-Tag)
# -1 beide werden nicht mitgezählt (so wie her gewünscht), dh 1.1 bis 2.1 = 0
# 0 ein ein Tag wird gezählt (also die Übernachtungen), so wie bei normaler Differenzbildung, dh 1.1 bis 2.1 = 1
# +1 beide werden voll mitgezählt, dh 1.1 bis 2.1 = 2
Gruß Daniel