Eine weitere Frage aus meinem Atlanta Power Excel-Seminar:
Wie können Sie die abrechnungsfähige Zeit bis zur nächsten Viertelstunde abrunden?
Angenommen, Sie führen ein Protokoll Ihrer abrechnungsfähigen Zeit. Sie haben eine Startzeit und eine Endzeit. Eine Formel wie =E2-D2berechnet die verstrichene Zeit.
Das Ergebnis in F2 ist 55 Minuten. Die Standardformatierung zeigt jedoch 00:55 Uhr an. Wählen Sie die gesamte Spalte. Drücken Sie Strg + 1, um die Zellen zu formatieren. Wählen Sie die Kategorie Zeit. Welche davon sollten Sie verwenden? Wählen Sie vorerst 13:30:55, aber Sie werden später das Problem mit dieser Auswahl sehen.
Ihre Unternehmensrichtlinie besteht darin, alle Abrechnungen bis zur nächsten Viertelstunde aufzurunden. Führen Sie diese Berechnung durch: Ein Tag hat 24 Stunden. Es gibt 4 Viertelstunden in einer Stunde. Multiplizieren Sie 24 * 4, um 96 zu erhalten. Das bedeutet, dass eine Viertelstunde 1/96 eines Tages entspricht.
Verwenden Sie die Formel =CEILING(F2,1/96), um die verstrichene Zeit in F auf die nächste Viertelstunde zu runden. In der Excel-Methode zum Speichern der Zeit beträgt der Mittag 0,5. 6 Uhr morgens ist 0,25. 00:15 Uhr ist 1/96 oder 0.010417. Es ist einfacher, sich an 1/96 als an 0.010417 zu erinnern.
Die Ergebnisse sind unten gezeigt. Beachten Sie, wie sich die 35 Minuten auf 45 Minuten aufrunden. Dies scheint die Formel zu sein, die mein Anwalt verwendet … haha, das ist nur ein Witz, Esquire Dewey! Verklage mich nicht! Ich weiß, dass sie verwenden =CEILING(F2,4)…
Hier pausieren… Dieser Artikel wird auf die nächste Viertelstunde aufgerundet, da dies die Methode ist, die Larry in meinem Seminar verwendet. Es besteht eine gute Chance, dass Ihre Firma zu etwas anderem rundet. Das Bild unten zeigt, wie auf fünf Minuten oder 1 Minute gerundet wird. Sie können eine ähnliche Logik verwenden, um auf die nächsten 6 Minuten (1/240) oder 12 Minuten (1/120) zu runden.
Auch - ein sanfter Vorschlag, der den Unterschied zwischen BODEN, MROUND und DECKEN zeigt. Die FLOOR-Funktion in B9: B22 rundet immer ab. Der MROUND in C9: C22 rundet auf den nächsten. Die DECKE in D9: D22 rundet ab.
Hinweis
In der Excel-QuickInfo wird vorgeschlagen, FLOOR.MATH oder CEILING.MATH anstelle von FLOOR oder CEILING zu verwenden. Die neuen ".Math" -Versionen dieser Funktionen behandeln negative Zahlen unterschiedlich. Da die Zeit in Excel niemals negativ sein kann, ist es in Ordnung, den normalen BODEN oder die DECKEN zu verwenden.
Ändern Sie den Bruch von 1/96 auf 1/288, um ihn in Schritten von fünf Minuten zu runden.
Zeiten in Stunden umrechnen
Ein weiterer Vorschlag von Larry: Konvertieren Sie die Zeiten in echte Stunden. Auf diese Weise funktioniert die Mathematik, wenn Sie mit der Abrechnungsrate pro Stunde multiplizieren. Nehmen Sie die gerundete verstrichene Zeit und multiplizieren Sie sie mit 24, um eine Dezimalzahl von Stunden zu erhalten. Stellen Sie sicher, dass Sie das Ergebnis als Zahl mit zwei Dezimalstellen und nicht als Zeit formatieren.
Welches Zeitformat soll verwendet werden?
Es ist Zeit, über diese Zeitformatierungsoptionen zu sprechen: Sie hätten 13:30, 13:30:55 oder 37:30:55 wählen können. In den obigen Screenshots habe ich 13:30:55 verwendet. Das Problem damit tritt am Ende der Woche auf. Wenn Sie die Dezimalstunden in Spalte H addieren, haben Sie 53 abrechnungsfähige Stunden. Wenn Sie die Zeiten in G addieren, erhalten Sie fünf Stunden. Was ist los?
Beides ist richtig, wenn Sie das von Ihnen ausgewählte Zahlenformat verstehen. Angenommen, Sie geben =NOW()eine Zelle ein. Sie können die Zelle so formatieren, dass Datum und Uhrzeit, nur Datum oder nur Uhrzeit angezeigt werden. Excel hat kein Problem damit, den Teil vom 29. Mai vom 29. Mai um 08:15:34 Uhr wegzulassen, wenn Sie ihm sagen, dass er das tun soll. Die Antwort in Zelle G57 lautet "2 Tage und fünf Stunden". Wenn Sie jedoch 13:37:55 als Zeitformat gewählt haben, haben Sie Excel angewiesen, die Anzahl der Tage wegzulassen und nur die Stunden, Minuten und Sekunden anzuzeigen.
Wenn Sie Spalte G in das Format 37:30:55 konvertieren, erhalten Sie die richtige Anzahl von Stunden:
Die Beschriftung über diesem Bild erfasst das kleine Problem im Format 37:30:55: Es werden Sekunden angezeigt, was unnötig erscheint, da wir auf die nächsten fünfzehn Minuten runden. Klicken Sie nach Auswahl von 37:30:55 auf die Kategorie Benutzerdefiniert. Sie können sehen, dass der Zahlenformatierungscode von (h):mm:ss;@verwendet wird. Die eckigen Klammern um das H sind der wichtige Teil.
Um Ihre Zeiten in Stunden und Minuten anzuzeigen, bearbeiten Sie das Zahlenformat, um Folgendes zu entfernen: ss.
Schau Video
Das heutige Video enthält am Ende einen Bonus-Tipp zum Formatieren von Dezimalstellen als Bruch.
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2210: Aufrunden auf die nächste Viertelstunde.
Hey, willkommen zurück bei Netcast, ich bin Bill Jelen. Heute noch eine Frage aus meinem Atlanta Power Excel Seminar. Jemand hat eine Abrechnungstabelle erstellt, und sie wollten auf die nächste Viertelstunde aufrunden, oder? Für jede Aufgabe hier haben sie einen Client, eine Startzeit und eine Endzeit. Die verstrichene Zeit zu berechnen - das ist einfach, wenn es sich um Echtzeitzeiten handelt oder wenn sie nur = E2-D2 sind. Das heißt also 55 Minuten, aber es sagt es auf eine wirklich seltsame Weise - "00:55 Uhr". Wir wollen dies formatieren. Wähle die Zeit und hey, schau, hier gibt es ein paar verschiedene Möglichkeiten: 13:30; 13:30:55, was die Sekunden zeigen würde; und dann 37:30:55. Im Moment werde ich die 13:30 wählen, aber wir werden später zurückkehren und diese Wahl erneut prüfen. Also wähle ich OK und doppelklicke, um es nach unten zu kopieren.und Sie sehen, dass es nur die Mathematik macht - also hier, 25 Minuten. Aber die Person, die diese Frage gestellt hat - die Firma, für die sie arbeiten -, möchte auf die nächste Viertelstunde aufrunden.
Also gut, Viertelstunde. Lassen Sie uns über Zeiten in Excel nachdenken. Der Mittag ist 0,5. Wenn ich das als Zeit formatiere - 12:00:00 Uhr. Und wenn ich 0,25 mache, ist das 6:00:00 Uhr. Okay, die Art und Weise, wie dies funktioniert, ist, dass ein ganzer Tag die Zahl 1.0 ist. Wenn wir also 15 Minuten herausfinden wollen, wäre es - es gibt 24 Stunden x 4 Viertelstunden, also müssten wir = setzen 1/96 drin, und das gibt uns 15 Minuten. Jetzt, 1/96 - ich kann diese Art von Mathe in meinem Kopf machen: 24x4; 1/96 - es ist sicherlich einfacher, sich an 1/96 zu erinnern, als sich an diese Dezimalstelle zu erinnern: 0.01041666667. Okay, es war also einfacher, 1/96 dort zu verwenden, und wir werden eine Funktion namens CEILING verwenden.
DECKE. Wir haben die Wahl zwischen CEILING und CEILING.MATH für positive Zahlen, die identisch sind. Die Eingabe von CEILING ist kürzer, daher werde ich CEILING verwenden. Und gerundet gibt es 1/96 eines Tages - das sind 15 Minuten - und das ist die richtige Antwort im falschen Format. Wir werden diese Formate kopieren - Strg + C und dann Alt + E + S + T, um spezielle Formate einzufügen - und die 55 Minuten werden auf die nächste Stunde gerundet. Doppelklicken Sie, um das nach unten zu kopieren. Die 35 Minuten werden auf die nächsten 45 Minuten aufgerundet. 25 Runden auf die nächsten 30 auf. Es scheint zu funktionieren, obwohl die Person, die diese Frage gestellt hat - sie auf die nächsten 15 aufrundet. Vielleicht verwendet sogar Ihr Unternehmen etwas anderes.
Okay, um auf die nächste Viertelstunde zu runden, verwenden wir 1/96. Aber um auf die nächsten 5 Minuten zu runden - es gibt 24 Stunden, es gibt 12 5-Minuten-Perioden in jeder Stunde, also 12x4 = 288. Verwenden Sie 1/288, um auf die nächsten 5 Minuten zu runden. Wenn Sie auf die nächsten 6 Minuten runden, gibt es 10 Zeiträume von 6 Minuten x 24 Stunden. Sie runden auf die nächste 1/240. Um auf die nächste Minute zu runden, wählen Sie auf die nächste 1/1440.
Also gut, Sie können dies tun, und wir haben auch die Wahl. CEILING wird sich immer aufrunden, also werden 16 Minuten - gehen wir dort zu 1/96 - 16 Minuten auf 30 aufrunden, und obwohl ich sicher bin, dass mein Anwalt der Meinung ist, dass dies eine großartige Möglichkeit zur Abrechnung ist, ist es das auch nicht wirklich fair zu mir - dem Kunden. Okay, Sie wissen also, dass CEILING abrunden wird. Mein Anwalt wird das nie benutzen: Dieser BODEN wird immer abgerundet. Aber wahrscheinlich ist es das Schönste, MROUND zu verwenden. Also, = MROUND der Zeit auf das nächste 1/96, und das wird für alles bis zu ungefähr 22,5 Minuten abgerundet; und danach wird es aufgerundet. Okay, also rundet es auf das ab, was es am nächsten ist. Sie müssen sich also entscheiden, ob Sie FLOOR, MROUND oder CEILING verwenden möchten, und dann, was Ihr Divisor sein wird - ob es 'Es wird auf die nächsten 5 Minuten, die nächste Minute oder, wissen Sie, was auch immer sein. Okay, da fassen wir zusammen.
Nun, ein Bonus-Tipp hier - das war Larry in diesem Seminar - Larry sagt: "Hey, sieh mal, es ist nicht wirklich gut, in solchen Zeiten abrechnungsfähige Zeit zu zeigen. Wir wollen wirklich in Stunden umrechnen, weil du wirklich gehst Um mit einer bestimmten Rate abrechnen zu können - Sie wissen, $ 35 / Stunde, $ 100 / Stunde, $ 500 / Stunde, unabhängig von Ihrer Rate - und wir müssen in der Lage sein, die Stunden x die Raten zu multiplizieren. Es wäre also schön, wenn in der Lage sein, diese Stunden in eine Dezimalzeit umzuwandeln. Also nehmen wir diese Zeit in G2 und multiplizieren sie mit 24, und das gibt uns eine Dezimalzahl von Stunden, aber es wird im falschen Format sein. Sie sind Wählen Sie in diesem Fall ein Zeitformat, wenn es überhaupt kein Zeitformat sein soll, sondern nur das Zahlenformat. Stellen Sie sicher, dass mindestens 2 Dezimalstellen angegeben sind. Doppelklicken Sie, um das Format zu kopieren, und wir bekommen unsere Zeiten. "
Okay, jetzt zurück zu unserer Frage hier: "Welches Zahlenformat sollten wir verwenden?", Dies ist ein wirklich guter Ort, um über "Warum?" Zu sprechen. Wenn wir alle diese Zahlen hier auswählen, werden wir sehen, dass die Summe 53 Stunden beträgt, und wenn ich hierher komme und eine SUMME-Funktion einsetze, erhalten wir 53 Stunden. Das ist perfekt. Wenn ich jedoch die SUMME-Funktion unterhalb einer Zeitspalte verwende und Alt + = oder AUTOSUM mache, habe ich 5 Stunden… 5 Stunden Zeit. Welches ist richtig - 53 oder 5?
Nun, es stellt sich in einem seltsamen Sinne heraus, dass beide Recht haben. Aber hier ist, warum sie beide Recht haben. Ich werde eine Funktion namens = NOW eingeben. Du wirst sehen, dass ich das tatsächlich am 9. Mai aufnehme, also vor 20 Tagen, und es ist 7:08 Uhr. Und mit = NOW möchte ich manchmal das Datum und die Uhrzeit anzeigen, aber manchmal möchte ich nur um das Datum anzuzeigen. Also kann ich machen = JETZT geben Sie mir das aktuelle Datum, oder ich kann machen = JETZT geben Sie mir die aktuelle Zeit. Daher können die Zahlenfunktionen entweder die Freizeit kürzen oder das Datum entfernen. Und dieser, den wir hier unten gewählt haben, der 13:30, sagt: "Hey, ich möchte die Anzahl der Tage nicht sehen; zeig mir nur die Zeit." Ja, das ist das Problem - denn das sind wirklich 2 Tage und 5 Stunden - 2 Tage sind 48 Stunden - 2 Tage und 5 Stunden. Und wir sagten es - wir sagten zu Excel: "Hey, mach dir nicht die Mühe, mir die Tage zu zeigen. "Nun, wir wussten nicht, dass wir Excel das erzählten, aber genau das sagten wir Excel, als wir dieses oder jenes wählten. Okay, also bist du dazu gezwungen Komm her bis 37:30:55. Das ist derjenige, der uns die volle Anzahl von Stunden zeigt, und wir bekommen die richtige Antwort von 53 Stunden.
Aber es scheint wirklich albern, diese zusätzlichen Sekunden zu zeigen, wenn wir auf 15 Minuten runden. Gehen wir also darauf zurück. Nachdem Sie 37:30:55 gewählt haben, um das Zahlenformat dahinter zu lernen, klicken Sie auf Benutzerdefiniert. Sie werden sehen, dass die eckigen Klammern rund um die Uhr funktionieren. Aber wir brauchen das: ss nicht. Jetzt können wir dieses benutzerdefinierte Format bearbeiten und haben Stunden und Minuten Zeit. Wir müssen heute nicht verstehen, was das; @ bedeutet. Es heißt, dass Sie für Text nur den Text anzeigen müssen. Aber lassen Sie diesen Teil einfach dort, auch wenn Sie ihn nicht verstehen. Klicken Sie auf OK, und wir haben jetzt unsere Zeit in Stunden und Minuten, wobei die Sekunden weggelassen werden. Aber die Summen funktionieren. Wirklich, wirklich subtil, diese 13:30 und diese 13:30:55. Weißt du, es heißt nicht: "Hey, du sagst Excel, dass es das Datum ignorieren soll … die ganzen Tage." Aber,Sie sind.
Dinge wie Zeit nehmen Mathe sind in diesem Buch: LEBEN: Die 54 besten Excel-Tipps aller Zeiten. Klicken Sie auf das "I" in der oberen rechten Ecke, um es zu überprüfen.
Das klang nach einer so einfachen Frage; Ich kann nicht glauben, dass wir schon 7 Minuten sind. Ein Unternehmen möchte jedoch in Viertelstunden abrechnen und immer auf die nächste Viertelstunde aufrunden. Also 24 Stunden am Tag, 24x4 = 96; 15 Minuten = 1/96. Verwenden Sie also CEILING (A2,1 / 96), um auf die nächsthöhere Viertelstunde aufzurunden. Verwenden Sie MROUND (A2,1 / 96), um auf die nächste Viertelstunde zu runden, und verwenden Sie dann FLOOR (A2,1 / 96), um auf die untere Viertelstunde zu runden - geht immer zurück. Ein Tag hat 24 Stunden, Sie können also 1/288 tun, um 5-Minuten-Perioden zu erreichen, 1/240, um 6-Minuten-Perioden zu erreichen, 1/1440, um auf die nächste Minute zu runden. Und dann der Bonus-Tipp dort am Ende (welches Zahlenformat) - Sie möchten immer zu 37:30:55 zurückkehren, wenn Sie Stunden über 24 Stunden anzeigen möchten.
Um die Arbeitsmappe aus dem heutigen Video herunterzuladen, besuchen Sie diese URL dort unten in der Youtube-Beschreibung.
Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Hallo! Danke fürs Mithängen. Aber hier noch ein Bonus-Tipp (den ich nicht einmal erwähnen werde). Womit ich hier angefangen habe - lassen Sie uns das Ganze klären - eine kleine Formel, die auf eine dieser Dezimalstellen zeigte - also die nächste Viertelstunde. Aber ich wollte das nicht als 0.010417 zeigen. Es ist fantastisch - in der Zahlenformatierung gibt es tatsächlich etwas, das als Brüche bezeichnet wird, und wenn ich "Bis zu zwei Ziffern" wähle, bekomme ich 1/96; Aber ich muss mit 1/288 umgehen, also wähle ich "Bis zu drei Ziffern" und es zeigt mir immer noch 1/96; Aber wenn ich dann zu diesem konvertiere - dem 1/288 - wird es korrekt angezeigt. Jetzt hätte ich wohl "vier Ziffern" machen sollen, um mit dem 1/1440 fertig zu werden. Was wird das machen? Das wird mir geben … Aber,Wenn ich in das Zahlenformat gehe und zu Benutzerdefiniert gehe und dort nur ein zusätzliches Fragezeichen hinzufüge, gelangen wir zum nächsten 1/1440 eines Bruchteils. Zahlenformat - coole, coole Sache.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: round-to-viertelstunde.xlsx
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Freunde lassen Freunde nicht INDIREKT verwenden"
Jordan Goldmeier
