Excel 2010 - Quersumme eines Datums

[Jisi]

Hallo, ich möchte die Quersumme eines Datums berechnen.
Dabei soll darauf geachtet werden, ob das Ergebnis < 22 , = 22 oder > 22 ist.

Quersumme < 22: Steht in Zelle A1 zB. "01.01.2012" dann soll in der Zelle B1 "7" stehen.
Quersumme = 22: Steht in Zelle A1 zB. "19.07.2012" dann soll in der Zelle B1 "0" stehen
Quersumme > 22: Steht in Zelle A1 zB "19.09.2012" dann soll in der Zelle B1 "6" stehen (Quersumme aus 24).

Zur Berechnung der Quersumme vom Jahr 2000-2099 benutze ich diese Formel:

=ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2

Ich habe dann versucht diese Formel wie folgt zu verschachteln:

=WENN(ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2<22;ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2;WENN(ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2=22;0;ABRUNDEN((ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2/10);0)+ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2-ABRUNDEN((ABRUNDEN((TAG(A3)/10);0)+TAG(A3)-ABRUNDEN((TAG(A3)/10);0)*10+ABRUNDEN((MONAT(A3)/10);0)+MONAT(A3)-ABRUNDEN((MONAT(A3)/10);0)*10+ABRUNDEN((JAHR(A3)/10);0)+JAHR(A3)-ABRUNDEN((JAHR(A3)/10);0)*10-200+2/10);0)*10))

Das Problem das nun besteht ist, wenn die Quersumme größer als 22 ist komme ich auf falsche Werte, der Rest funktioniert wie er soll. Zudem ist die Formel auch noch schwer überschaubar, weiß jemand vielleicht einen Rat oder sogar eine bessere, überschaubare Formel dafür? Ich bin dankbar für jede Hilfe.

 

[evilbaschdi]

mich wundert, dass das überhaupt funktioniert. weil immerhin ist das datum nur eine formatierung mit einem "normalen" zahlenwert im hintergrund. evtl. scheitert es ja daran.
hätte aber dann aber darauf getippt, dass alles über dem wert 22 liegt und nicht unterhalb.

 

[DJND]

Ohne jetzt viel Erfahrung mit Excel zu haben und die vorhandenen Funktionen zu kennen:

1. Entfernen aller nichtnumerischen Zeichen, so daß nur 0 bis 9 übrig bleibt (Punkt fliegt raus)
2. Die resultierende Zahl Zeichen für Zeichen ansprechen, also 1. Zeichen + 2. Zeichen + ....

Das sollte definitiv weniger komplex sein wie deine Formel da. Alternativ würde ich modulare Berechnung vorschlagen. D.h. Fallweise Berechnung. IF MONAT = 1 THEN QUERSUMME += 1 ELSEIF MONAT = 2 THEN... ELSEIF MONAT = 12 THEN QUERSUMME += 3 ENDIF

 

[Minimax83]

Hallo,

Oder so:

=SUMME(1*TEIL(TEXT(A1;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))

 

[Jisi]

Vielen Dank euch allen für eure schnellen Antworten. Ich bin jetzt auf eine funktionierende Lösung gekommen:

=WENN(SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))<22;SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1));WENN(SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))=22;0;ABRUNDEN((SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))/10);0)+SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))-ABRUNDEN((SUMME(1*TEIL(TEXT(A2;"TTMMJJJJ");{1.2.3.4.5.6.7.8};1))/10);0)*10))

So rechnet er alles in Zelle B1 mit den 3 Bedingungen fehlerfrei aus. Das geht wahrscheinlich auch noch schönerer, doch die Hauptsache ist, dass es funktioniert. Vielen Dank für die Hilfe.

 

[DerKleine49]

@
Was glaubst du warum Microdoft in den Officeprogrammen einen Asssitenten, F1 oder Rechts oben: die Hilfetexteingabe programmiert haben? Sinnlose Fragerei anstatt die TASTE "F1" zu drücken! kopfschüttel
Du musst bei Textrechenaufgaben die " " verwenden. Steht allles in den Formelassistenen.

 

[GMG-CC]

Moin,

und für alle, die keine "meterlangen" Formeln mögen hier eine Funktion, die (aus meiner Sicht) etwas übersichtlicher ist:

Option Explicit

Function DatumQuerSumme(Datum As Date) As Integer
   Dim Rc As Integer, i As Integer
   Dim sDatum As String, sRc As String
   
   sDatum = Format(Datum, "DDMMYYYY")
   For i = 1 To 8
      Rc = Rc + Mid(sDatum, i, 1)
   Next i
   If Rc < 22 Then
      DatumQuerSumme = 7
   ElseIf Rc = 22 Then
      DatumQuerSumme = 0
   Else
      sRc = Format(Rc, "00")
      DatumQuerSumme = (Left(sRc, 1) * 1) + (Right(sRc, 1) * 1)
   End If
End Function