Fehler bei der Formatierung nach Zeit in Excel suchen

395
user173897

Ich habe eine Kolumne von Zeiten, die sich über 24 Stunden erstrecken (nicht unbedingt um Mitternacht oder Mittag). Die erste wird manuell ausgefüllt und dann wird jede nachfolgende nach der Formel " [prevcell]+TIME(0,30,0)" automatisch gefüllt, wobei die vorherige Zelle die Zelle ist, die sich unmittelbar über der aktuellen Zelle befindet. Die erste Zelle wird immer auf der Stundenmarke stehen, also für einige ganze Zahlen die Form "n: 00: 00" haben. Daher sind alle nachfolgenden Zeiten entweder auf der neuen Stundenmarke oder auf der Dreißig-Minuten-Marke, was bedeutet, dass sie die Form "n: m0: 00" haben, wobei n eine ganze Zahl ist und m 0 oder 3 ist.

Dann habe ich versucht, die Zellen im bedingten Format so zu verwenden, dass jede Zelle, die sich auf einer dreißigminütigen Marke befindet (id est: hat die Form "n: 30: 00" für Ganzzahl n), eine hellgraue Hintergrundfarbe (während alle anderen) Zellen sind weiß / keine Farbe). Eine Zelle mit 10:00:00 wird beispielsweise weiß sein, aber eine Zelle mit 10:30:00 wäre hellgrau.

Ich tat dies:

  1. Regeltyp: "Nur Zellen formatieren, die enthalten".

  2. Zellen formatieren mit: "cell value", "gleich", " =TIME(HOUR([curcell]), 30, 0)". (Wo die aktuelle Zelle die bestimmte Zelle ist, die gerade formatiert wird.)

  3. Ausgewählte geeignete Hintergrundfarbenformatierung.

Die Formatierung funktionierte für die meisten Zellen. Ich würde vielleicht verstehen, wenn es aus irgendeinem Grund nicht für die erste oder letzte Zelle funktionierte, aber normalerweise auch. Für was es nicht funktioniert, sind genau die Zellen mit den folgenden Werten: 11:30:00, 18:30:00 (18:30 Uhr), 21:30:00 (21:30:00 Uhr), 22 : 30: 00 Uhr (10:30 Uhr); Diese Zellen sind nicht farbig (weiß), wenn sie farbig sein sollen. Es funktioniert für alle anderen Zellen, einschließlich 23:30:00 Uhr (11:30:00 Uhr), obwohl es für 11:30:00 Uhr (morgens) für die Zelle nicht funktioniert hat. Es gibt keine Zellen, die gefärbt sind, wenn sie nicht sein sollten; Die einzigen Zellen, die nicht gefärbt sind, wenn sie sein sollten, sind die zuvor genannten.

Wenn ich den Startwert / die Zeit in meiner obersten Zelle ändere, treten unterschiedliche Fehler der gleichen Art auf (oft aber nicht immer für dieselben Werte und manchmal für zusätzliche).

Weiß jemand, was falsch ist oder wie man das beheben kann?

Bearbeiten! Hier sind einige Beobachtungen, die ich hatte:

  • Nach der nächsten Mitternacht funktioniert es nicht, egal wie spät es in meiner ersten Zelle ist (außer 00:00:00). Die Abhängigkeit vom Wert der ersten Zelle scheint folgendermaßen zu gehen:
  • 11:30 Uhr (morgens) arbeitet zu bestimmten Zeiten in meiner ersten Zelle.
    1. Es funktioniert nicht für 00:00:00, 00:30:00, 1:00:00, 1:30:00 und 2:00:00.
    2. Es funktioniert für 2:30, 3:00, 3:30.
    3. Es funktioniert nicht für 4:00, 4:30, 5:00, 5:30, 6:00, 6:30.
    4. Es funktioniert für alle Zeiten nach 7:00 Uhr und erhöht sich um 30 Minuten bis einschließlich 11:30 Uhr. (Nach diesem Startwert gilt keine Überlegung mehr).
  • Ich habe nicht ausgiebig nachgesehen, aber aus einem kurzen Blick: 18:30:00, 21:30:00 und 22:30:00 arbeiten Sie nie.
  • Abhängig vom Wert meiner ersten Zelle funktionieren bestimmte andere n: 30: 00-Zeiten nicht (für Ganzzahl n), aber ich kann kein Muster entdecken. Soweit ich das auch sagen kann, gibt es keine Fehler für beliebige n: 00: 00-Zeiten (für ganzzahlige n).

Update Nr. 1

Ich habe die Formatierungsbedingung folgendermaßen geändert: Formatieren von Zellen mit "Zellwert", "nicht gleich", " =TIME(HOUR([curcell]), 0, 0)". (Wo die aktuelle Zelle die bestimmte Zelle ist, die gerade formatiert wird.)

Dies scheint die Situation etwas verbessert zu haben, jedoch nicht perfekt. Es formatiert alles korrekt, mit der Ausnahme, dass die Zellen mit dieser Zeit nicht korrekt gefärbt werden: 20:30:00 (20:00 Uhr), 23:00:00 (23:30 Uhr) und (falls vorhanden) alle Zeiten nach und einschließlich der nächsten Mitternacht (24:00:00; aka: die nächsten 12:00 Uhr).

( Edit : Die Zeiten, zu denen es Farben falsch darstellt, scheinen vom Wert meiner ersten Zelle und möglicherweise von anderen Faktoren abzuhängen. Aber es gibt immer zwei diskrete Fehler später am Tag und das Problem nach Mitternacht.)

Update Nr. 2

Das Hauptproblem wurde gelöst. Wenn mir jemand sagen kann, warum es überhaupt entstanden ist, wäre ich sehr dankbar. Neugierige Köpfe wollen es wissen!

1
Lassen Sie mich eine einfache Frage stellen, ob Sie alle Zellen in grauer Farbe haben möchten, wenn der Minutenanteil der ZEIT NUR 30 ist. Rajesh S vor 6 Jahren 0
Wenn es nicht "00" ist, wird es "30" sein, je nachdem, wie das Blatt eingerichtet ist. Wenn Sie also eine Lösung haben, die alles färbt, was nicht "00" im Minutenteil ist (und nur Minuten- "00" bleibt), dann funktioniert das. user173897 vor 6 Jahren 0
Beachten Sie, dass die Formel "Zellenwert ist nicht gleich = TIME (HOUR ([Curcell]], 0, 0))" ist, funktioniert besser, aber färbt nicht richtig 20:00:00, 23:00 und alles nach (und einschließlich) 24 : 00: 00 (die nächste Mitternacht), falls diese Werte vorhanden sind. (Also habe ich die Formel geändert, weil sie besser ist. Aber das grundlegende Problem bleibt trotzdem bestehen.) user173897 vor 6 Jahren 0
^^ Die oben genannten Zeiten, für die es nicht funktioniert, hängen von der Zeit in meiner ersten Zelle ab (und möglicherweise von anderen Faktoren). Aber es gibt immer zwei diskrete Fehler und dann das Problem nach Mitternacht (vielleicht wird daraus ein Kürbis?). user173897 vor 6 Jahren 0

2 Antworten auf die Frage

2
barry houdini

Ich gehe davon aus, dass dieses Problem durch die Art und Weise verursacht wird, in der Excel berechnet, siehe diesen Link

https://support.microsoft.com/de-de/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

Wenn Sie also Zeiten hinzufügen, ist das Ergebnis nicht exakt auf der Minute, aber etwas darüber oder darunter, so dass Sie keine genaue Übereinstimmung mit Ihrem Vergleich erhalten

Die MINUTEFunktion funktioniert, wenn der Wert etwas übersteigt, aber nicht darunter liegt. Sie können meine Theorie überprüfen, indem Sie SECONDfür Ihre Daten eine Funktion verwenden, um zu sehen, ob Sie Werte von 59 haben

Für eine Korrektur, wenn Sie nur ganze Minuten benötigen, können Sie zur nächsten Minute runden und dann den Minutenwert überprüfen, z. B. für einen Zeitwert in A2

=MINUTE(MROUND(A2,"0:01"))=30

Aktualisieren:

Ich überprüfte dies, indem ich Zeitwerte in eine Spalte setzte und dann Ihre Formel in der nächsten Spalte verwendete. Wo meine Zeitwerte bei A2 anfingen, habe ich diese Formel in B2 nach unten kopiert

=A2=TIME(HOUR(A2),30,0)

Für 18:30 bekomme ich FALSEund wenn ich die Werte überprüfe, ist der Wert in A2 dieser

0,770833333333334

aber der Wert von TIME(HOUR(A2),30,0)ist dieser:

0,77083333333333 3

Also keine Übereinstimmung, es sei denn, Sie runden die Daten vor dem Vergleich ab

Gute Arbeit! Vielen Dank! Das hat mich verrückt gemacht! user173897 vor 6 Jahren 0
1
Rajesh S

Überprüfen Sie den Screenshot unten, und zeigen Sie an, dass der MINUTE-Teil in den Zellen, falls gewünscht, in 30 Farben in Farbe formatiert ist.

Folge diesen Schritten.

  1. Wählen Sie den Datenbereich aus, in dem Sie die ZEIT eingeben möchten.
  2. Übernehmen Sie das Zellenformat als HH: MM: SS und geben Sie dann die TIME-Werte ein.
  3. Wählen Sie den DATENBEREICH, um das bedingte Format anzuwenden.
  4. Klicken Sie in der Home-TAB auf Bedingte Formatierung, Neue Regeln und schließlich Formel verwenden, um die zu formatierende Zelle zu bestimmen.
  5. Schreiben Sie diese Formel =MINUTE(A2)=30und wählen Sie dann die Farbe mit OK aus, wobei A2 zum aktuell ausgewählten Datenbereich gehört.

NB: Denken Sie zum besseren Verständnis nur an Spalte B. Ich habe TIME PERIODs erwähnt, da Sie auch geschrieben haben, dass die bedingte Formatierung nach MID NIGHT und anderen nicht funktioniert.

Ebenfalls, in Spalte C, fand ich Minuten von den Daten in Col A nur zu zeigen, und die Werte in benachbarten Zelle zu entsprechen, um sicherzustellen, dass meine Lösung nur Formatierung Zelle in der grauen Farbe, wenn der Wert Minute 30 nur.

Hoffe die Arbeit für dich.

Hinweis: Alternativ können Sie auch die oben beschriebenen Schritte befolgen und anstelle der =MINUTE(A2)=30Formel können Sie auch diese verwenden =TIME(0,MINUTE(A2),0)=TIME(0,30,0).

Ich habe noch nie "Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen", um für mich zu arbeiten. Einschließlich dieser Zeit. :( user173897 vor 6 Jahren 0
Gehen Sie zu Bedingte Formatierung und klicken Sie auf Neue Regeln. Klicken Sie darauf. Ein Fenster wird geöffnet. Wählen Sie den Regeltyp aus. Wählen Sie den letzten aus. Verwenden Sie eine Formel, um die zu formatierenden Zellen zu bestimmen. Klicken Sie auf das Symbol. Bearbeiten Sie die Regelbeschreibung und klicken Sie dann auf das Feld Text Geben Sie die von mir vorgeschlagene Formel ein. Rajesh S vor 6 Jahren 0
Hoffe du hast es gefunden. Rajesh S vor 6 Jahren 0
Ich habe das gemacht, aber es hat nicht funktioniert. Ihre Idee gab mir jedoch (nach einiger Arbeit) eine meiner eigenen, die funktionierte: Tun Sie, was Sie vorgeschlagen haben, aber die Formel lautet: `= Zeit (0, Minute ([Curcell]), 0) = Zeit (0, 30,0) `. Also werde ich Ihre Antwort akzeptieren. :) Vielen Dank! user173897 vor 6 Jahren 0
Schön, von Ihnen zu hören, es ist in Ordnung, dass Sie die Formel Ihrer Wahl angewendet haben. Suchen Sie nach einem Problem, es gibt viele mögliche Lösungen. Ich habe die Minutenformel und die Daten verwendet, die Sie in OP angezeigt haben, und den Screenshot hochgeladen. Rajesh S vor 6 Jahren 1

Verwandte Probleme