Excel - Welche Formel wenn ich in einer Zelle Buchstaben und Zahlen habe?
Hallo Zusammen, ich habe ein Problem.
Ich habe eine Datei auf Arbeit, bei der ich die Zahlungsziele errechnen muss.
Bsp: N030 , ME15 , Prepayment
Gegeben sind:
- Voraussichtliches Lieferdatum
- N030 = netto 30 Tage
- ME15 = Monatsende + 15 Tage
- Prepayment (also Vorkasse)
Ich habe mir also extra Spalten angelegt und dort erstmal ausgegliedert:
- Zahlungsziel ME: =WENN(ISTZAHL(SUCHEN("M";M4));"M";"")
- Zahlungsziel N: =WENN(ISTZAHL(SUCHEN("N";M4));"N";"")
- Zahlungsziel Prepayment: =WENN(ISTZAHL(SUCHEN("PREPAYMENT";M4));"PREPAYMENT";"")
- Zahlungsziel Tage: =TEIL(M4;3;2)
Also habe ich eine Formel bei der Spalte Voraussichtliches Zahlungsziel geschrieben:
=WENN(ISTZAHL(SUCHEN(O4;M4));I4+Q4;WENN(ISTZAHL(SUCHEN(N4;M4));I4+J4+Q4;""))
Da "N" gleich netto heißt, konnte ich dort sagen, Lieferdatum + die Anzahl der Tage.
Bei "ME" musste ich noch 2 Spalten hinzufügen:
- Monatsende: =WENN(H2="";"";MONATSENDE(H2;0))
- Tage: =WENN(I2="";"";I2-H2)
Nun ist aber noch das Problem mit dem Prepayment.
Ich kriege die Formel nicht so hin, dass er mir dort gleich das Lieferdatum zieht.
Könnte mir bitte jemand helfen?
Dankschön
3 Antworten
Mal ganz davon abgesehen, dass ich dir mit deiner Tabelle nicht helfen kann:
Hast du gerade etwa sensible Kundendaten geleakt? Du weißt hoffentlich schon, dass das Konsequenzen wie z.B. eine Kündigung oder eine Datenschutzklage haben kann.
- M = Zahlungsziel
- N = Zahlungsziel
- P = Zahlungsziel
- Q = Zahlungsziel
- .
- J = Tage?
Fragen
- Woraus resultieren die Tage ("J")
- Woraus NO10 / NO14?
- Woraus ZZ MW ("N")
- Woraus ZZ N ("O")
- Zahlungsziel Tage, vermutlich aus Spalte "L"
- Voraussichtliches Zahlungsdatum - aus Spalte I + Q
.
Ich bin nicht sicher ob ich Dich und Deine Tabelle richtig verstanden habe
Ganz habe ich es nicht verstanden, doch das ist jedenfalls vergiftet:
Zahlungsziel Tage: =TEIL(M4;3;2)
Datum und Zahlen werden intern völlig verschieden gespeichert. Greife also nie auf diese Weise zu und verknüpfe die miteinander.
Lege Dir drei Spalten an: Zahlungsziel - faktische Zahlung - Tage Differenz. Die letzte Spalte ist ein Datum! Sieht aus wie eine Zahl, ist aber keine.
Diese Spalte kopierst Du als Wert. Also etwa: A3 = A5 und formatierst es als Zahl. Daraus kannst Du dann gut die Rechnungssumme rechnen.
Natürlich ist es auch eine Zahl! Doch keine Zahl, die zu behandeln wäre, wie alle anderen. Etwa zeigen negative Werte auf ein Datum vor dem 1.1.1900. Denn sie zählt Tage, keine Dezimale. Du kannst diese Zahl nicht ungestraft mit jeder anderen verknüpfen. Wenn Du damit noch nie auf die Nase gefallen bist, es dennoch zu tun, - dann programmierst Du noch nicht lange.
Oder ich denke einfach logisch, dass ein Datum oder eine Zeit niemals negativ sein kann und fange dies vorher ab z.B. mit der Vorzeichen Funktion und Excel und Programmiersprachen unterscheiden sich dennoch ein wenig voneinander, vor allem was die Komplexität angeht, ich habe jedenfalls in Excel sehr selten einen 2000 Zeiligen Code in der Formelzelle. Und wenn du schon Zahl! und Zahl unterscheidest, dann hättest du auch schreiben können, dass ein Datum nicht zur Menge der Ganzzahlen gehört, dann wäre das deutlich transparenter gewesen, besonders für Laien vor allem aber nachlesbar, was damit genau gemeint ist.
Denn einem Excel Einsteiger tiefgreifende Programmierkenntnisse zu unterstellen ist "mutig", ich glaube hätte er die, würde er eine solche Frage nicht stellen.
Natürlich kann die Zahl, die ein Datum repräsentiert, negativ sein. Eben dann, wenn es vor dem 1.1.1900 ist. Weil die Zahl, die für genau dieses Datum steht, - genau, die Null ist. Ein -1 wäre dann der 31.12.1899
Und ja, ein Datum gehört zu den ganzen Zahlen, denn der interne Speicher eines Datums zählt nach dem julianischen Datum die Anzahl der Tage. Tückischerweise jedoch nicht seit Christi Geburt, sondern seit dem 1.1.1900.
Ich kann nur ein zig-tes mal warnen, es einfach als Zahl zu verknüpfen.
Und um es noch übler zu machen, ist sehr oft nach dem Komma - die Zeit kodiert.
Also 0,1 wäre dann die Geisterstunde des 1.1.1900.
Mit anderen Worten: Es gibt selbst für die Zahl, die ein Datum repräsentiert, noch verschiedene Formate und zwar mit und ohne Uhrzeit.
Daher: Verknüpfe es nicht mit anderen Zahlen, auch wenn es vorerst geht.
ich kann deinen Schlussfolgerungen von der Oberfläche Excel, technisch nicht so ganz wirklich folgen, denn 1. musste ich noch nie mit Daten vor dem 1.1.1900 rechnen, denn in aller Regel bezieht man sich auf Daten die einigermaßen in einem Menschenleben liegen, gut wenn ich die Differenz zum Datum zur Fahrt mit der ersten Eisenbahn brauche, dann mag das interessant sein.
Natürlich kann die Zahl, die ein Datum repräsentiert, negativ sein. Eben dann, wenn es vor dem 1.1.1900 ist. Weil die Zahl, die für genau dieses Datum steht, - genau, die Null ist. Ein -1 wäre dann der 31.12.1899
Und nochmal ich rede von Excel von der Oberfläche, weder VBA noch eine andere Programmiersprache, nur das was der User sieht.
Und genau das sehe ich hier nicht, denn gebe ich -1 ein und formatiere es als Datum erhalte ich Folgerichtig #######...
Da es für Excel eben nicht als Datum bekannt ist, wie kann ich also mit Daten vor dem 01.01.1900 rechnen und mir ein relatives Verhältnis dazu nicht reicht, tue ich mich mit Excel sehr schwer.
Wie rechne ich also dieses Datum nun Praxisbezogen als Zahl um, mit der ich im Verhältnis zu den Daten ab dem 01.01.1900 nichts anfangen kann, das einzige was mir einfällt wäre folgendes ich nehme irgend ein Datum vor dem 01.01.1900 z.B. so =DATUM(1899;12;31+1) und errechne mir eine neue Referenz:
Denn dann erhalte ich als Ergebnis nicht 1 sondern 693963, nun hätte ich also ein Referenzdatum, was aber leider falsch ist den der 31.12.1899, ist leider nicht 31.12.1899 sondern Excel zeigt mir als Datum dem 31.12.3799 an, bekomme ich also hier noch zuverlässige Daten (Stichpunkt Schaltjahre).
Was ich damit sagen will, egal wo ich die Grenze ziehe, ich werde ab einem gewissen Punkt immer Probleme haben mit den Daten zu rechnen. Denn ab einem gewissen Wert fängt Excel wieder an die Daten als Werte aus den 2000ern zu interpretieren.
Nicht im Programmiersprachen aber in Excel.
Könntest du mir also sagen wie ich jetzt mit diesen Werten sinnvoll rechnen kann
Nehmen wir mal an mich interessiert die Differenz zwischen heute und dem Mord auf Caesar, wie rechne ich das nun, ohne eine super Komplexe eigene Formel zu schreiben indem ich mir eine Referenzwert ab dem Tage 0 des julianische Kalenders baue und die mit der Excel Datums-Logik vergleiche, wenn du dafür einen Trick hast bin ich ganz Ohr. Auch wenn ich es noch nie gebraucht habe, aber was nicht ist, kann ja noch kommen..
Rechne ich hingegen mit =heute()+5 ist das zwar eben jene Referenz ab dem 01.01.1900, aber es ist klar und eindeutig ab heute 5 Tage und das interessiert, mich doch als Nutzer, dass mir in Wahrheit Excel nur die Zeit ausblendet, diese aber von 0,0 bis 0,99 usw. darstellt denke ich ist jedem klar, das macht es aber eben einfach und transparent. X,1 wäre logischerweise der 10te Teil eines Tages also 144min oder anders ausgedrückt 8640 Sekunden des aktuellen Tages. Natürlich nur sofern größer als 1, denn den 00.01.1900 wird es wohl nicht geben.
Programmiersprachen machen es doch eigentlich nicht viel anders, außer dass sie negative Werte nicht stumpf abblocken, aber auch die haben eine Referenzzeit 0, meist in Millisekunden ab Datum X.
Vielleich bin ich auch einfach nur doof und will das Problem nicht sehen und du kannst mir ein Beispiel zeigen bei dem ich wirklich Probleme habe.
Wie gesagt rechne ich 19:00 - minus 22:00 ist klar, dass ich dann Probleme bekomme, da ich dann nur mit einer Zeit Rechne und die nur zwischen 0 und 0,99... variiert, rechne ich jedoch mit =heute()+Zeit(12;0;0) und subtrahiere davon =Zeit(18;0;0) oder in anderen Worten 0,75, dann erhalte ich 18 Uhr des Vortages.
Aber ist das nicht Logik?
Wäre daher gespannt auf ein Beispiel, ich bin bei weitem nicht frei von Denkfehlern.
Hallo, diese Formel Zahlungsziel Tage: =TEIL(M4;3;2) ist nur dafür da, um den Teil der Zahl aus den Zahlungsbedingungen zu ziehen, denn diese heißen ja entweder N030 oder ME15.
Ohne diese bekomm ich die Zahl leider nicht allein stehend.
Aber trotzdem danke, ich habe die richtige Lösung gefunden :-)
Das musst du mir mal erklären, warum ein Datum keine Zahl sein soll, natürlich es eine Zahl und zwar die Anzahl der Tage ab dem 01.01.1900, lässt sich ganz einfach feststellen indem man mal das Format Datum entfernt und auf Allgemein oder Standard oder Zahl klickt.
Ich lasse mich gerne eines besseren belehren, ich hatte aber in meiner ganzen Excel Zeit noch die Probleme mit einem Datum zu rechnen.