Excel-Formel, um 4.15 als 4 Stunden und 15 Minuten zu interpretieren

516
Gray Roberts

Ich erstelle ein Arbeitszeitblatt für meine Firma und jede einzelne Zelle enthält die IN-Zeit und die OUT-Zeit, dargestellt als "9-3".

Ich habe eine Funktion erstellt, die die Zeichenfolge von 6 Tagen SUMME und den numerischen Unterschied zwischen 9 und 3 berechnet, wobei 3 als 15 innerhalb der Formel berechnet wird. Dies funktioniert gut als flache Zeit, jedoch unterbricht die Zeitinterpretation, wenn etwas anderes als eine ganze Zahl eingeschlossen wird. "9.30-3" gibt eine Zahl zurück, die nicht als korrekte Zeit interpretiert wird. Dies ist verständlich, da ich die Zahlen nicht als Zeit registriere. Dies ist jedoch vorwiegend auf das Format der Quelldaten zurückzuführen, dh den Bindestrich.

Ich verstehe, dass die Zeitberechnung aufgelöst wird, indem das richtige Dezimal-Timing verwendet wird und die Zelle neu formatiert wird, um die interpretierte Zeit anzuzeigen. Die Art meiner Quelldaten lässt dies jedoch nicht zu, oder ich muss zumindest eine ungeheure Gleichung ändern, um sie korrekt darzustellen quantifizieren Sie die Zeit von der Dezimalstelle.

Außerdem ist es wichtig zu wissen, dass die eingegebenen Informationen werden im Format von 4,15 für 16.15, nicht 15 / 100stel der Stunde als Excel wird es als gelesen.

2
Jedes Mal in einen "TIMEVALUE" einwickeln. Daher gibt `= (TIMEVALUE (D1) - TIMEEVALUE (D2)) 'die Differenz. Siehe diese Anleitung. https://www.ablebits.com/office-addins-blog/2015/06/24/calculate-time-excel/ Alex vor 7 Jahren 1
@Alex, Ihr Vorschlag übersieht das Problem, dass das Trennzeichen eine Dezimalzahl ist und kein Doppelpunkt. fixer1234 vor 7 Jahren 0
Wie unterscheidet Excel 4.15 als 4:15 PM vs. 4:15 AM (nur durch Testen der relativen Werte der Start- und Endzeiten)? fixer1234 vor 7 Jahren 0
Wenn Sie die Zeitformatierung in der Zelle angeben, sollte Excel den Rest erledigen. Für 4.15 wird immer angenommen, dass es sich um 4.15 Uhr handelt. Wenn Sie jedoch die Zelle für das 12-Stunden-Format formatieren und 16.15 eingeben, wird sie in Ordnung gebracht. Alle Zeiten und Datumsangaben werden im 24-Stunden-Format gespeichert. Sie müssen lediglich die Formatierung darauf anwenden. In diesem Fall besteht das Problem darin, dass es bereits eine riesige Gleichung gibt, die geändert werden müsste. Dies führt jedoch zu einem möglichst wartbaren Ergebnis. Alex vor 7 Jahren 0
"Die eingegebenen Informationen werden für 16:15 Uhr im Format 4.15 sein" - was passiert also um 4.15 Uhr? Alex vor 7 Jahren 0
Vielen Dank für Ihre Anregungen! Das war sehr nützlich und ich habe eine Antwort angekreuzt, die funktioniert hat - ich versuche immer noch, sie in eine einzelne Zelle zu sortieren, ohne dass Fehler auftreten. Tatsächlich habe ich folgendes: `= SUMME (TIMEVALUE (SUBSTITUTE (LINKS (A4, FIND (" - ", A4) -1),". ",": "))) - (TIMEVALUE (SUBSTITUTE (MID (A4, FIND.) ("-", A4) +1,9999), ".", ":"))) `- Dies zieht die gesamte Zeit nach dem Bindestrich ein, zieht die Zahl von der Zeit vor dem Bindestrich ab und ersetzt dann die Dezimalstelle des Ergebnisses für den erforderlichen Doppelpunkt. Gray Roberts vor 7 Jahren 0

2 Antworten auf die Frage

3
Gary's Student

Sagen wir, wir haben in A1 :

8.45-2.23

In B1 geben Sie ein:

=--LEFT(A1,FIND("-",A1)-1)

und in C1 eingeben:

=--MID(A1,FIND("-",A1)+1,9999)

Diese beiden Zellen sind "dezimale" Versionen der Start- und Stoppzeiten. In D1 passen wir für AM / PM an:

=IF(C1<B1,C1+12,C1)

Zum Schluss geben Sie in E1 ein:

=TIMEVALUE(SUBSTITUTE(D1,".",":"))-TIMEVALUE(SUBSTITUTE(B1,".",":"))

Mit angemessener Formatierung angewendet.

Diese können auf Wunsch zu einer einzigen Formel kombiniert werden:

=TIMEVALUE(SUBSTITUTE((IF((--MID(A1,FIND("-",A1)+1,9999))<(--LEFT(A1,FIND("-",A1)-1)),(--MID(A1,FIND("-",A1)+1,9999))+12,(--MID(A1,FIND("-",A1)+1,9999)))),".",":"))-TIMEVALUE(SUBSTITUTE((--LEFT(A1,FIND("-",A1)-1)),".",":"))
Dies ist eine großartige Antwort und ich schätze es sehr. Ich versuche jedoch, alles in eine einzige Zelle zu passen und mit allen auftretenden Fehlern fertig zu werden. Wird mein endgültiges Einzelzellenergebnis posten! Gray Roberts vor 7 Jahren 0
@ GrayRoberts Siehe meine Bearbeitung Gary's Student vor 7 Jahren 0
Das ist wirklich großartig - Danke dafür !! Ich war abwesend, weil ich mein Bestes gegeben habe, um es zum Laufen zu bringen, aber ich glaube nicht, dass es praktisch funktioniert. Die Person, die dies verwenden wird, wird wahrscheinlich jeden Zeitwert eingeben, der zwischen 9-3 und 9.15-9.30 liegt. Ich denke nicht, dass Ihre Gleichung berücksichtigt wird, wenn eine Dezimalzahl nicht verwendet wird. Ich schätze Ihre Zeit wirklich sehr, aber ich habe solche Schwierigkeiten und Sie sind die einzige Person, die ich gefunden habe und die mich in die richtige Richtung weist. Also vielen Dank! Gray Roberts vor 7 Jahren 0
@GrayRoberts Wenn du mehr Hilfe brauchst, ist morgen gut ..... Ich reise heute. Gary's Student vor 7 Jahren 0
Du bist ein echter Held. Vielen Dank, dass Sie so schnell geantwortet haben - ich freue mich auf morgen und hoffe, dass Sie heute sichere Reisen haben! Gray Roberts vor 7 Jahren 0
Um Ihnen ein klareres Bild von meinem Problem zu geben, muss ich insgesamt sechs Stunden Arbeitszeit für eine Person in einer Reihe finden. Ich finde, der eindeutigste und zuverlässigste Weg besteht darin, die Time-Funktion einzugeben und dann die Zelle nach Zahlen auf jeder Seite eines Bindestrichs zu durchsuchen. Es gibt jedoch einige Variablen, und ich habe wirklich Schwierigkeiten, alles zusammenzufügen . Ich versuche, dass eine einzelne Zelle eine Mischung von Einträgen berechnet - als Beispiel; Montag: "9-3" (6 Stunden) Dienstag: "9.15-3" (5h 45m) Mittwoch: "9.15-3" (5h 45m) Donnerstag: "9.15-3.30" (6h 15m) und so weiter und ich bin so festgefahren! Gray Roberts vor 7 Jahren 0
0
Gray Roberts

Okay, ich habe es tatsächlich geschafft, dies selbst zu tun. Mir wurde klar, dass das Zeitformat nicht so leicht aus der Zelle gezogen werden konnte, also entschloss ich mich, die Elemente der Zelle zu analysieren. Das heißt, von einer Zelle, die "9.30-5.15" im Textformat liest, brauche ich Excel, um Folgendes zu verstehen:

  • Schichtbeginn um 9 Uhr
  • & 30 Minuten
  • & 0 Sekunden
  • Schicht nach 5 Stunden verlassen
  • & 15 Minuten
  • & 0 Sekunden
  • 5 Stunden sind weniger als 9 Stunden, addieren Sie also 12 Stunden, um 24 Stunden zu berücksichtigen
  • Ziehen Sie die jetzt größere Zeit von der jetzt weniger Zeit ab

Die folgende Formel ist ziemlich hässlich anzusehen, funktioniert aber auf jeden Fall (bis zu einem gewissen Punkt).

=IF(ISBLANK(A1),TIME(0,0,0),IFERROR(IF((--MID(A1,FIND("-",A1)+1,9999))<=(--LEFT(A1,FIND("-",A1)-1)),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(MID(A1,1,SEARCH(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,SEARCH(".*-",A1)+1,SEARCH("-",A1)-SEARCH(".*-",A1)-1),0),0)+TIME(12,0,0),TIME(IFERROR(MID(A1,FIND("-",A1)+1,LEN(A1)-SEARCH(".",A1,SEARCH("-",A1))-1),RIGHT(A1,LEN(A1)-FIND("-",A1))),IFERROR(RIGHT(A1,LEN(A1)-SEARCH(".",A1,FIND("-",A1))),0),0)-TIME(IFERROR(LEFT(A1,FIND(".*-",A1)-1),(LEFT(A1,FIND("-",A1)-1))),IFERROR(MID(A1,FIND(".",A1)+1,SEARCH(".*-",A1)),0),0)),(TIME(0,0,0))))

Mit dieser Formel kann eine leere Zelle als Nullzeit gezählt werden, und auch eine mit Wörtern bestückte Zelle wird als Null gezählt, um die Berechnung nicht zu ruinieren.

Dies dient dazu, eine Monatsrunde zu erstellen, und die Zeit wird in den folgenden Formaten eingegeben:

  • 9-3
  • 9.30-3
  • 9-3.30
  • 9.30-3.30

Dies bedeutet, dass der Schichtleiter mal eingeben (die mit täglicher Varianz ändern können) und das Blatt von 6 Tage der Woche berechnen, die Berechnung hervorheben, wenn ihre Stunden-Quote durch die bedingte Formatierung zu erreichen, wie im Bild: Zeittabelle Rota Berechnung

Hoffe, das hilft jedem, der versucht, dasselbe zu tun !! Es gibt nur einen Fehler, mit dem ich mich nicht befasst habe - Sie können zum zweiten Mal keine zweistellige Stunde eingeben. Sie können also 3-9.59 eingeben, aber nicht 3-10. Das ist einfach, weil niemand, wo ich diese Vergangenheit 06.00 für Arbeiten bin der Gestaltung, und ich habe nicht brauchen zu reparieren es .. warum also die Mühe. Es ist jedoch nicht zu schwierig zu beheben, wenn Sie es aussuchen möchten, um es zu verwenden.

Bitte lassen Sie mich wissen, ob dies für Sie von Nutzen ist, da ich mein Gehirn dazu verstrickt habe, diese Arbeit so zu gestalten, dass sie seit einiger Zeit funktioniert.

Wenn jemand interessiert ist, kann ich eine Kalendervorlage 2017 mit allen diesen Berechnungen senden. Lass es mich wissen, denn es ist ein großer Vorteil für uns. Gray Roberts vor 7 Jahren 0

Verwandte Probleme