Un'altra domanda dal mio seminario Atlanta Power Excel:
Come puoi arrotondare il tempo fatturabile al quarto d'ora successivo?
Supponi di tenere un registro del tuo tempo fatturabile. Hai un'ora di inizio e un'ora di fine. Una formula come =E2-D2calcolerà il tempo trascorso.
Il risultato in F2 è di 55 minuti. Ma la formattazione predefinita mostra 12:55. Scegli l'intera colonna. Premi Ctrl + 1 per formattare le celle. Seleziona la categoria Ora. Quale di questi dovresti usare? Per ora, scegli 13:30:55, ma vedrai più tardi il problema con questa selezione.
La politica aziendale prevede l'arrotondamento di tutte le fatture al quarto d'ora successivo. Fai questo calcolo: ci sono 24 ore in un giorno. Ci sono 4 quarti d'ora in un'ora. Moltiplica 24 * 4 per ottenere 96. Ciò significa che un quarto d'ora è equivalente a 1/96 di un giorno.
Usa la formula =CEILING(F2,1/96)per arrotondare il tempo trascorso in F fino al quarto d'ora successivo. Nel metodo di archiviazione dell'ora di Excel, Mezzogiorno è 0,5. 6:00 è 0,25. 00:15 è 1/96 o 0,010417. È più facile ricordare 1/96 di 0,010417.
I risultati sono mostrati sotto. Notate come i 35 minuti arrivino a 45 minuti. Questa sembra essere la formula che usa il mio avvocato … haha, è solo uno scherzo Esquire Dewey! Non farmi causa! So che usano =CEILING(F2,4)…
Fermandomi qui … questo articolo verrà arrotondato al prossimo quarto d'ora perché questo è il metodo utilizzato da Larry nel mio seminario. Ci sono buone probabilità che la tua azienda si rivolga a qualcos'altro. L'immagine sotto mostra come arrotondare a cinque minuti o 1 minuto. È possibile utilizzare una logica simile per arrotondare ai 6 minuti (1/240) o 12 minuti (1/120) più vicini.
Inoltre - un suggerimento gentile che mostra la differenza tra FLOOR, MROUND e CEILING. La funzione FLOOR in B9: B22 viene sempre arrotondata per difetto. Il MROUND in C9: C22 arrotonda al più vicino. Il CEILING in D9: D22 arrotonda per eccesso.
Nota
La descrizione comando di Excel suggerirà di utilizzare FLOOR.MATH o CEILING.MATH invece di FLOOR o CEILING. Le nuove versioni ".Math" di queste funzioni gestiscono i numeri negativi in modo diverso. Poiché il tempo in Excel non può mai essere negativo, va bene usare il normale PAVIMENTO o SOFFITTO.
Modificare la frazione da 1/96 a 1/288 per arrotondare a incrementi di cinque minuti.
Conversione dei tempi in ore
Un altro suggerimento di Larry: converti i tempi in ore reali. In questo modo, quando moltiplichi per il tasso di fatturazione all'ora, la matematica funzionerà. Prendi il tempo trascorso arrotondato e moltiplicalo per 24 per ottenere un numero decimale di ore. Assicurati di formattare il risultato come un numero con due cifre decimali e non come un'ora.
Quale formato dell'ora utilizzare?
È ora di parlare di queste scelte di formattazione dell'ora: avresti potuto scegliere 13:30, 13:30:55 o 37:30:55. Negli screenshot sopra, ho usato 13:30:55. Il problema con questo appare alla fine della settimana. Quando si sommano le ore decimali nella colonna H, si hanno 53 ore fatturabili. Quando totalizzi i tempi in G, ottieni cinque ore. Cosa sta succedendo?
Entrambi sono corretti, se comprendi il formato numerico che hai selezionato. Di 'che entri =NOW()in una cella. Puoi formattare la cella per mostrare data e ora, solo data o solo ora. Excel non ha problemi a lasciare la parte del 29 maggio del 29 maggio 08:15:34 se gli dici di farlo. La risposta nella cella G57 è "2 giorni e cinque ore". Ma quando hai scelto 13:37:55 come formato dell'ora, stavi dicendo a Excel di lasciare il numero di giorni e di mostrare solo le ore, i minuti ei secondi.
Se converti la colonna G per utilizzare il formato 37:30:55, ottieni il numero corretto di ore:
La didascalia sopra quell'immagine cattura il piccolo problema con il formato 37:30:55: mostra i secondi, il che sembra inutile dato che stiamo arrotondando ai quindici minuti più vicini. Dopo aver scelto 37:30:55, fai clic sulla categoria Personalizzato. È possibile vedere il codice di formattazione del numero (h):mm:ss;@utilizzato. Le parentesi quadre intorno alla H sono la parte importante.
Per mostrare i tuoi tempi in ore e minuti, modifica il formato del numero per rimuovere: ss.
Guarda un video
Il video di oggi contiene un suggerimento bonus alla fine per la formattazione dei decimali come frazione.
Trascrizione del video
Impara Excel dal podcast, episodio 2210: round up to the next quarter hour.
Ehi, bentornato al netcast, sono Bill Jelen. Oggi, un'altra domanda dal mio seminario Atlanta Power Excel. Qualcuno stava facendo un foglio di calcolo per la fatturazione e volevano arrotondare per eccesso al quarto d'ora successivo, giusto? Quindi, per ogni attività qui, hanno un cliente, ora di inizio e ora di fine e per calcolare il tempo trascorso, è semplice, se sono tempi reali o se sono solo = E2-D2. Quindi, significa 55 minuti, ma in un modo davvero strano: "00:55". Vogliamo andare avanti e formattare questo. Scegli l'ora e, ehi, guarda, ci sono alcune scelte diverse qui: 13:30; 13:30:55, che mostrava i secondi; e poi 37:30:55. Ora, per ora, sceglierò le 13:30, ma torneremo indietro più tardi e riesamineremo quella scelta. Quindi, scelgo OK e faccio doppio clic per copiarlo,e vedi che sta solo facendo i conti - quindi ecco, 25 minuti. Ma la persona che ha posto questa domanda, l'azienda per cui lavorano, vuole arrotondare al quarto d'ora successivo.
Va bene, quindi, un quarto d'ora. Pensiamo ai tempi in Excel. Mezzogiorno è 0,5. Se lo formatto come ora, 12:00:00 PM. E se faccio 0,25, saranno le 6:00:00. Va bene, quindi il modo in cui funziona è che un giorno intero è il numero 1.0, quindi, se volessimo calcolare 15 minuti, sarebbe-- ci sono 24 ore x 4 quarti d'ora, quindi dovremmo mettere = 1/96 lì dentro, e questo ci dà 15 minuti. Ora, 1/96 - Posso fare i conti nella mia testa: 24x4; 1/96 - è certamente più facile ricordare 1/96 che ricordare questo decimale: 0,01041666667. Va bene, quindi era più facile usare 1/96 lì, e quindi useremo una funzione chiamata CEILING.
SOFFITTO. Vedi, abbiamo una scelta tra CEILING e CEILING.MATH per numeri positivi identici. È più breve digitare CEILING, quindi userò CEILING. E, arrotondato, c'è 1/96 di un giorno, ovvero 15 minuti, e questa è la risposta giusta, nel formato sbagliato. Copieremo questi formati su - Ctrl + C, e poi Alt + E + S + T per incollare formati speciali - e i 55 minuti verranno arrotondati all'ora successiva. Fare doppio clic per copiarlo e, vedi, i 35 minuti vengono arrotondati per eccesso ai successivi 45; 25 arrotonda per eccesso ai successivi 30. Sembra che stia funzionando, anche se la persona che ha posto questa domanda - sta arrotondando al prossimo 15. Forse anche la tua azienda usa qualcosa di diverso.
Va bene, quindi per arrotondare al quarto d'ora più vicino, usiamo 1/96. Ma per arrotondare ai 5 minuti più vicini, ci sono 24 ore, ci sono 12 periodi da 5 minuti in ogni ora, quindi 12x4 = 288. Usa 1/288 per arrotondare ai 5 minuti più vicini. Se arrotondi ai 6 minuti più vicini, ci sono 10 periodi da 6 minuti x 24 ore; arrotondi al 1/240 più vicino. Per arrotondare al minuto più vicino, selezionare al 1/1440 più vicino.
Va bene, quindi puoi farlo e anche noi abbiamo una scelta. CEILING arriverà sempre per eccesso, quindi 16 minuti - andiamo a 1/96 lì - 16 minuti arriveranno a 30 e, anche se sono sicuro che il mio avvocato pensa che sia un ottimo modo per fatturare, è non proprio giusto con me, il cliente. Va bene, quindi sai che CEILING verrà arrotondato. Il mio avvocato non lo userà mai: questo PIANO verrà sempre arrotondato per difetto. Ma probabilmente la cosa più giusta qui è usare MROUND. Quindi, = MROUND del tempo al 1/96 più vicino, e questo verrà arrotondato per difetto a circa 22,5 minuti; e poi, dopo, verrà arrotondato. Va bene, quindi arrotonda a tutto ciò a cui è più vicino. Quindi, devi scegliere se utilizzare FLOOR, MROUND o CEILING, e poi quale sarà il tuo divisore …sarà ai 5 minuti più vicini, al minuto più vicino o, sai, qualunque cosa. Va bene, quindi ci stiamo radunando.
Ora, un suggerimento bonus qui - questo era Larry in quel seminario - Larry dice: "Ehi, guarda, non è molto bello mostrare il tempo fatturabile in tempi come questi; vogliamo davvero convertirci in ore perché stai davvero per fatturare a una certa tariffa (sai, $ 35 / ora, $ 100 / ora, $ 500 / ora, qualunque sia la tua tariffa) e dobbiamo essere in grado di moltiplicare le ore x le tariffe. Quindi, sarebbe carino essere in grado di convertire quelle ore in un tempo decimale. Quindi, prendiamo quel tempo in G2 e lo moltiplichiamo per 24, e questo ci darà un numero decimale di ore, ma sarà nel formato sbagliato. Sono sceglierà un formato ora in questo caso, quando non dovrebbe essere affatto un formato ora; dovrebbe essere solo in formato Numero. E assicurati di includere almeno 2 posizioni decimali. Fai doppio clic per copiarlo e abbiamo i nostri tempi ".
Va bene, ora, tornando alla nostra domanda qui di "Quale formato di numero dovremmo usare?", Questo è davvero un buon posto per parlare di "Perché?". Se scegliamo tutti questi numeri, qui, vedremo che la somma è di 53 ore, e se scendo qui e inserisco una funzione SOMMA, otteniamo 53 ore. È perfetto. Ma, se sto usando la funzione SUM sotto una colonna di tempo e faccio Alt + = o AUTOSUM, mi dà 5 ore… 5 ore. Qual è la risposta giusta, 53 o 5?
Bene, si scopre in qualche strano senso, hanno ragione entrambi. Ma ecco perché hanno ragione entrambi. Vado a inserire una funzione chiamata = NOW. Vedrai che in realtà lo sto registrando il 9 maggio, quindi, 20 giorni fa, e sono le 7:08 e con = ORA, a volte voglio mostrare la data e l'ora, ma altre volte, voglio solo per mostrare la data. Quindi, posso fare in modo che = ORA mi dia la data corrente, oppure posso fare in modo che = ORA mi dia l'ora corrente. E così, le funzioni numeriche sono felici di troncare il tempo libero o rimuovere la data. E questo che abbiamo scelto quaggiù, le 13:30, dice: "Ehi, non voglio vedere il numero di giorni; mostrami solo l'ora". Sì, questo è il problema, perché in realtà sono 2 giorni e 5 ore, 2 giorni sono 48 ore, 2 giorni e 5 ore. E l'abbiamo detto, abbiamo detto a Excel "Ehi, non preoccuparti di mostrarmi i giorni. "Beh, non sapevamo di averlo detto a Excel, ma è quello che stavamo dicendo a Excel quando abbiamo scelto questo o questo. Va bene, quindi sei costretto a vieni qui alle 37:30:55, è quello che ci mostrerà il numero completo di ore e otteniamo la risposta corretta di 53 ore.
Ma sembra davvero sciocco mostrare quei secondi extra quando stiamo arrotondando a 15 minuti. Quindi, torniamo indietro. Dopo aver scelto 37:30:55 per conoscere il formato del numero dietro questo, fare clic su Personalizzato. Vedrai che la cosa che lo fa funzionare sono le parentesi quadre intorno all'ora. Ma non abbiamo bisogno di: ss. Quindi, ora possiamo modificare quel formato personalizzato e quindi abbiamo ore e minuti. Non abbiamo bisogno di capire oggi cosa significa; @. Sta dicendo che, per il testo, mostra solo il testo; ma lascia quella parte lì, anche se non la capisci. Fare clic su OK e ora abbiamo il nostro tempo in ore e minuti, tralasciando i secondi. Ma i totali funzionano. Davvero, davvero sottile, quelle 13:30 e quelle 13:30:55. Sai, non dice "Ehi, stai dicendo a Excel di ignorare la data … i giorni interi". Ma,siete.
Cose come Taking Time Math sono in questo libro: LIVe: The 54 Greatest Excel Tips Of All Time. Fare clic su quella "I" nell'angolo in alto a destra per verificarlo.
Sembrava una domanda così facile; Non posso credere che siamo già passati 7 minuti. Tuttavia, un'azienda vuole fatturare in un quarto d'ora e vuole sempre arrotondare al quarto d'ora successivo. Quindi, 24 ore in un giorno, 24x4 = 96; 15 minuti = 1/96. Quindi, usa CEILING (A2,1 / 96) per arrotondare al quarto d'ora superiore successivo. Usa MROUND (A2,1 / 96) per arrotondare al quarto d'ora più vicino, quindi usa FLOOR (A2,1 / 96) per arrotondare al quarto d'ora inferiore - torna sempre indietro. Ci sono 24 ore in un giorno, quindi puoi fare 1/288 per arrivare a periodi di 5 minuti, 1/240 per arrivare a periodi di 6 minuti, 1/1440 per arrotondare al minuto più vicino. E poi, il suggerimento bonus lì alla fine (quale formato numero): vuoi sempre tornare a quel 37:30:55 se hai intenzione di mostrare ore superiori a 24 ore.
Per scaricare la cartella di lavoro dal video di oggi, visita l'URL in basso nella descrizione di Youtube.
Voglio ringraziarti per essere passato; ci vediamo la prossima volta per un altro netcast da.
Hey! Grazie per avermi aspettato. Ma, solo un altro suggerimento bonus qui (che non ho nemmeno intenzione di menzionare). Quello con cui ho iniziato qui-- chiariamo tutta questa cosa-- una piccola formula che è arrivata e puntava a uno di questi decimali-- quindi, il quarto d'ora più vicino. Ma volevo dimostrarlo non come 0,010417. È fantastico - in Number Formatting, c'è effettivamente qualcosa chiamato Frazioni, e se scelgo "Fino a due cifre", ottengo 1/96; ma devo gestire quel 1/288, quindi scelgo "Fino a tre cifre" e mi mostra ancora 1/96; ma poi quando mi converto a questo - il 1/288 - lo mostrerà correttamente. Ora, immagino che avrei dovuto fare "quattro cifre" per gestire 1/1440. Cosa farai? Questo mi darà … Ma,se vado in Formato numero e vado su Personalizzato e aggiungo solo un punto interrogativo in più, arriveremo al 1/1440 di frazione più vicino. Formato numero: bella, bella cosa.
Scarica il file Excel
Per scaricare il file excel: round-to-quarter-hour.xlsx
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Gli amici non consentono agli amici di utilizzare INDIRETTO"
Jordan Goldmeier
