Excelformeln - FORMELN - Übersicht - Datum/Zeit (1) - Datumsdifferenz von Zeiten vor 1900 / vor Chr.

Das grosse Archiv mit 582
VBA-freien Formellösungen und Tips

Datum/Zeit (1) ->

Datumsdifferenz von Zeiten vor 1900 / vor Chr. (1)

Aufgabe
Wie lange liegen die Iden des März (Cäsars Ermordung) zurück ?
Microsofts Zeitrechnung ist in Excel bekanntlich stark begrenzt.
Um herauszufinden, wielange ein Ereignis genau zurückliegt, ist einige Vorarbeit nötig.

Beispiel:
Cäsar wurde am 15.März 44 vuZ ermordet.
Das war am 18.9.2001 genau 2045 Jahre, 11 Monate und 3 Tage her

	A	B	C	D	E	F
1
2
3		2045,925
4		1900
5		0	18.09.2001	a.c.n.	2045	Jahre
6		0	15.03.0044		11	Monate
7		37152	v		3	Tage
8		44,20833
9		15.03.44
10		0

Lösung
in C5 steht das heutige Datum bzw. das spätere
in C6 steht das alte Datum
rechtsbündig formatieren und führende Nullen mit eingeben - kleiner 1900; größer 2078 ist das Text
in C7(;;; formatiert) steht v (vor Christus) oder nichts / in D5 steht =WENN(C7="v";" a.c.n.";"")

Spalte B = Hilfszellen (ausblenden)
B3: =TAGE360(B5;B7)/360+B4-B6+B8+B10
B4: =WENN(C7="v";1900;WENN(ISTTEXT(C6);1900-ABRUNDEN(RECHTS(C6;4);-2);0))
B5: =WENN(C7="v";0;WENN(ISTTEXT(C6);LINKS(C6;6)&RECHTS(C6;4)+B4;C6))
B6: =WENN(ISTTEXT(C5);1900-ABRUNDEN(RECHTS(C5;4);-2);0)
B7: =WENN(ISTTEXT(C5);LINKS(C5;6)&RECHTS(C5;4)+B6;C5)
B8: =WENN(C7<>"v";0;TAGE360(B5;B9)/360)
B9: =LINKS(TEXT(C6;"TT.MM.JJJ");6)&RECHTS(TEXT(C6;"TT.MM.JJJ");4)-B10
B10: =WENN(C7="v";ABRUNDEN(RECHTS(WENN(ISTTEXT(C6);C6;TEXT(C6;"TT.MM.JJJ"));4);-2);0)

Ergebnis:
E5: =GANZZAHL(B3)
F5: =WENN(E5=0;"";WENN(E5=1;" Jahr";" Jahre"))

E6: =GANZZAHL(RUNDEN(12*REST(B3;1);7))
F6: =WENN(E6=0;"";WENN(E6=1;" Monat";" Monate"))

E7: =RUNDEN(360*(REST(B3;1)-E6/12);7)
F7: =WENN(E7=0;"";WENN(E7=1;" Tag";" Tage"))

Jetzt müssen wir aber noch darüber philosophieren, ob es das Jahr Null gab und wie (ab wann wir die Schaltjahre berücksichtigen: Gregor; Julian; Cäsar; Ramses II); - ich bin für nur Gregor!
Aber: es geht immerhin um Tage !

Tagesdifferenz ohne Berücksichtigung von Zeiten vor Christus:
späteres Datum in A1 / früheres in A2

führende Nullen bei Datum vor 1900 müssen eingegeben werden:
=WENN(ISTTEXT(A1);DATUM(TEIL(A1;7;4)+4000;TEIL(A1;4;2);TEIL(A1;1;2));DATUM(JAHR(A1)+4000;MONAT(A1);TAG(A1)))-WENN(ISTTEXT(A2);DATUM(TEIL(A2;7;4)+4000;TEIL(A2;4;2);TEIL(A2;1;2));DATUM(JAHR(A2)+4000;MONAT(A2);TAG(A2)))

als Array-Formel:
{=SUMME((LINKS(TEXT(A1:A2;"TT.MM.");6)&TEIL(TEXT(A1:A2;"TT.MM.JJJJ");7;4)+2000)*{1;-1})}

ohne führende Nullen - 7.1.801 geht auch:
=WENN(ISTTEXT(A1);DATUM(TEIL(A1;FINDEN("#";WECHSELN(A1;".";"#";2))+1;10)+4000;WECHSELN(TEIL(A1;FINDEN(".";A1)+1;2);".";);LINKS(A1;FINDEN(".";A1)-1));DATUM(JAHR(A1)+4000;MONAT(A1);TAG(A1)))-WENN(ISTTEXT(A2);DATUM(TEIL(A2;FINDEN("#";WECHSELN(A2;".";"#";2))+1;10)+4000;WECHSELN(TEIL(A2;FINDEN(".";A2)+1;2);".";);LINKS(A2;FINDEN(".";A2)-1));DATUM(JAHR(A2)+4000;MONAT(A2);TAG(A2)))

als Array-Formel:
{=SUMME((LINKS(TEXT(A1:A2;"T.M.");SUCHEN("-";WECHSELN(TEXT(A1:A2;"TT.MM.JJJJ");".";"-";2)))&TEIL(TEXT(A1:A2;"TT.MM.JJJJ");SUCHEN("-";WECHSELN(TEXT(A1:A2;"TT.MM.JJJJ");".";"-";2))+1;4)+2000)*{1;-1})}

Die Varianten von DATEDIF sind natürlich auch möglich - z.B. Monatsdifferenz:
=DATEDIF(WENN(ISTTEXT(A2);DATUM(TEIL(A2;FINDEN("#";WECHSELN(A2;".";"#";2))+1;10)+4000;WECHSELN(TEIL(A2;FINDEN(".";A2)+1;2);".";);LINKS(A2;FINDEN(".";A2)-1));DATUM(JAHR(A2)+4000;MONAT(A2);TAG(A2)));WENN(ISTTEXT(A1);DATUM(TEIL(A1;FINDEN("#";WECHSELN(A1;".";"#";2))+1;10)+4000;WECHSELN(TEIL(A1;FINDEN(".";A1)+1;2);".";);LINKS(A1;FINDEN(".";A1)-1));DATUM(JAHR(A1)+4000;MONAT(A1);TAG(A1)));"m")

zurück