MS Excel Problem?
Ich soll in der Arbeit eine Datenanalyse mit MS Excel durchführen. Ei dafür notwendiger Zwischenschritt ist es, die "Ist-Ankunftszeit" von Bussen von der "Soll-Ankunftszeit" eben dieser Züge zu subtrahieren (um die Verspätungen auszuwerten). Die Zellen, in denen diese Daten stehen, sind als "Standard" formatiert und sehen so aus:
2024-02-09T13:34
Teils steht auch noch die Sekundenzahl bei der Uhrzeit dabei, aber auch nicht immer (teils steht sie sogar bei der Sollzeit dabei und bei der Istzeit nicht, was auch noch so ein Aspekt ist). Ich will eben die Differenz von zwei solchen (oben in schwarz dargestellten) Daten bilden, um die Verspätung zu erhalten. Natürlich klappt das so erstmal nicht, es kommt die Fehlermeldung "Wert". Ich hab dann versucht, die entsprechenden Zellen als Zahl, Datum oder auch benutzerdefiniert zu formatieren, dennoch erhalte ich bei der Subtraktion immer den obigen Fehler. Excel erkennt halt nicht, was es tun soll. Auch die Funktion Datevalue hat mir nicht geholfen. Meines Erachtens sind die Daten bei uns da einfach schlecht aufbereitet^^
Hat jemand eine schlaue Idee, wie ich das Problem lösen kann?
Händisch einzeln ändern geht übrigens nicht, meine Exceltabelle hat 200.000 Zeilen, es geht hier um große Datenmengen.
Schönes Wochenende euch :)
5 Antworten
Leider kenne ich keine richtig einfache Möglichkeit so einen als Text gespeicherten Zeitstempel in Excel in einen entsprechenden Zeit- bzw. Datumswert umzuwandeln.
Wenn die Einträge alle ein Format der Form
2024-02-09T13:34
bzw. mit Sekunden
2024-02-09T13:34:00
haben, so könnte man beispielsweise für einen solchen in Zelle A1 gespeicherten Text die Formel
=DATEVALUE(MID(A1;1;10))+TIMEVALUE(MID(A1;12;8))
verwenden. Diese spaltet "2024-02-09T13:34:00" in "2024-02-09" und "13:34:00" auf und addiert den Datumswert von "2024-02-09" zum Zeitwert "13:34:00" um einen entsprechenden Zeit- bzw. Datumswert zu liefern.
Wenn du beispielsweise den Ist-Wert "2024-02-09T13:34" in Zelle A1 gespeichert hast und den Soll-Wert "2024-02-09T12:36:47" in Zelle B1 gespeichert hast, kannst du beispielsweise für die Differenz (Soll-Wert minus Ist-Wert) in Zelle C1 die Formel
=DATEVALUE(MID(A1;1;10))+TIMEVALUE(MID(A1;12;8))-DATEVALUE(MID(B1;1;10))-TIMEVALUE(MID(B1;12;8))
verwenden. Das Ergebnis ist in Tagen. Das kannst du dann entsprechend in eine Einheit umrechnen, die dir passt, oder in Excel die entsprechende Zelle als Zeit Formatieren, um die das im Format "hh:mm:ss" anzeigen zu lassen.
====== Ergänzung ======
Ich verwende Excel in Englisch. Wenn du Excel in Deutsch verwendest, brauchst du die Formel
=DATWERT(TEIL(A2;1;10))+ZEITWERT(TEIL(A2;12;8))-DATWERT(TEIL(B2;1;10))-ZEITWERT(TEIL(B2;12;8))
statt der Formel
=DATEVALUE(MID(A1;1;10))+TIMEVALUE(MID(A1;12;8))-DATEVALUE(MID(B1;1;10))-TIMEVALUE(MID(B1;12;8))
====== Alternative ======
Du könntest alternativ auch mit der Suchen-und-Ersetzen-Funktion probieren, das „T“ durch ein Leerzeichen zu ersetzen. Dann sollte Excel die Daten als Zeit- bzw. Datumswerte erkennen können, wenn die Formatierung richtig eingestellt ist.
Da GutenTag2003 das schon recht gut in seiner Antwort beschrieben hat, erspare ich mir, das weiter auszuführen...
https://www.gutefrage.net/frage/ms-excel-problem#answer-542353586
Meines Erachtens sind die Daten bei uns da einfach schlecht aufbereitet^^
Meines Erachtens nicht. Es handelt sich hier um ein ISO-8601-Datetime, ein sehr verbreitetes und standardisiertes Format.
Leider kapiert das Excel bis heute nicht. Microsoft halt.
Und daher muss man ein bisschen basteln, etwa so: https://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html oder so https://gist.github.com/edonosotti/c079a92d05dc6b6ecf2a56e0a6b79f13
Es klingt so, als ob du Schwierigkeiten dabei hast, die Zeitdaten in deiner Excel-Tabelle richtig zu interpretieren und zu verarbeiten. Hier sind ein paar Schritte, die dir vielleicht helfen könnten:
1. **Text in Spalten trennen:** Wenn die Zeitdaten im Format "YYYY-MM-DDTHH:MM" vorliegen, könntest du versuchen, diese in separate Spalten für Datum und Uhrzeit zu trennen. Dafür könntest du die Funktion "Text in Spalten" verwenden und das Trennzeichen "T" auswählen.
2. **Umwandlung in Zeitformat:** Nachdem du die Zeitdaten in separate Spalten getrennt hast, könntest du sicherstellen, dass sie als Zeitwerte formatiert sind. Du könntest dies mit der Funktion "Zeitwert" tun oder einfach das Zahlenformat auf "Uhrzeit" ändern.
3. **Berechnung der Differenz:** Sobald die Zeitdaten richtig formatiert sind, kannst du die Differenz zwischen der "Ist-Ankunftszeit" und der "Soll-Ankunftszeit" berechnen. Dies kannst du einfach durch Subtraktion der beiden Zeitwerte tun.
Wenn das nicht funktioniert, könnte es sein, dass es noch andere Probleme mit den Daten gibt, die behoben werden müssen. Wenn du möchtest, kannst du mir gerne mehr Details über die Fehlermeldung oder die Struktur der Daten geben, und ich kann versuchen, dir weiter zu helfen.
Teils steht auch noch die Sekundenzahl bei der Uhrzeit dabei, aber auch nicht immer (teils steht sie sogar bei der Sollzeit dabei und bei der Istzeit nicht, was auch noch so ein Aspekt ist). Ich will eben die Differenz von zwei solchen (oben in schwarz dargestellten)
Das liegt an Deiner Formatierung z.B. so
Zunächst kannst Du Deine Daten Excel-gerecht aufbereiten
- Datum das "-" gegen einen "Punkt" - über suchen/ersetzen
- T gegen ein Blank (Leer) ersetzen lassen
Danach
- Soll und Ist-Zeit mit Datum und Sekunden
- Differenz - da dürfte Minuten und Sekunden ausreichend sein.
A1 text: ist ---> 2024-02-09T13:34:27
C1 uhrzeit: ---> =ZEITWERT(TEIL(A1;12;8)) ergibt 13:34:27
B1 text: soll ---> 2024-02-09T13:15
D1 uhrzeit: ---> =ZEITWERT(TEIL(B1;12;8)) ergibt13:15:00
E1 zahl: verspätung ---> =(C1-D1)*24*60 ergibt 19.45 als differenz in minuten
19.45 sind 19min27sec, weil 0.45*60=27
Uhrzeiten um Mitternacht herum (wenn der Tag wechselt) müßten händisch nachbearbeitet werden.