O altă întrebare din seminarul meu din Atlanta Power Excel:
Cum puteți rotunji timpul facturabil până în următorul sfert de oră?
Spuneți că păstrați un jurnal al timpului facturabil. Aveți o oră de început și o dată de sfârșit. O formulă precum cea care =E2-D2va calcula timpul scurs.
Rezultatul în F2 este de 55 de minute. Dar formatarea implicită arată 12:55 AM. Alegeți întreaga coloană. Apăsați Ctrl + 1 pentru a formata celule. Selectați categoria Oră. Pe care dintre acestea ar trebui să le folosiți? Deocamdată, alegeți 13:30:55, dar veți vedea mai târziu problema cu această selecție.
Politica companiei dvs. este de a rotunji toate facturările până în următorul sfert de oră. Faceți acest calcul: există 24 de ore pe zi. Există 4 sferturi de ore într-o oră. Înmulțiți 24 * 4 pentru a obține 96. Asta înseamnă că un sfert de oră este echivalent cu 1/96 dintr-o zi.
Utilizați formula =CEILING(F2,1/96)pentru a rotunji timpul scurs în F până la următorul sfert de oră. În metoda Excel de stocare a timpului, Noon este 0,5. 6AM este 0,25. 00:15 este 1/96 sau 0,010417. Este mai ușor să ne amintim 1/96 decât 0,010417.
Rezultatele sunt prezentate mai jos. Observați cum cele 35 de minute se rotunjesc până la 45 de minute. Aceasta pare a fi formula pe care o folosește avocatul meu … haha, asta e doar o glumă Esquire Dewey! Nu mă da în judecată! Știu că folosesc =CEILING(F2,4)…
O pauză aici … acest articol se rotunjește până la următorul sfert de oră, deoarece aceasta este metoda pe care o folosește Larry în seminarul meu. Există mari șanse ca compania dvs. să treacă la altceva. Imaginea de mai jos arată cum se rotunjește la cinci minute sau 1 minut. Puteți utiliza o logică similară pentru a rotunji la cele mai apropiate 6 minute (1/240) sau 12 minute (1/120).
De asemenea - o sugestie ușoară care arată diferența dintre ETAJ, MROUND și Tavan. Funcția FLOOR din B9: B22 se rotunjește întotdeauna în jos. MROUND în C9: C22 se rotunde la cel mai apropiat. PLAFONUL din D9: D22 se rotunjește.
Notă
Sfatul de lucru Excel vă va sugera să utilizați FLOOR.MATH sau CEILING.MATH în loc de FLOOR sau CEILING. Noile versiuni „.Math” ale acestor funcții gestionează diferit numerele negative. Deoarece timpul în Excel nu poate fi niciodată negativ, este bine să folosiți podeaua obișnuită sau tavanul.
Schimbați fracția de la 1/96 la 1/288 pentru a rotunji la pași de cinci minute.
Conversia timpilor în ore
O altă sugestie de la Larry: convertiți orele în ore reale. În acest fel, când vă înmulțiți cu rata de facturare pe oră, calculele vor funcționa. Luați timpul scurs rotunjit și multiplicați cu 24 pentru a obține un număr zecimal de ore. Asigurați-vă că formatați rezultatul ca un număr cu două zecimale și nu cu o oră.
Ce format de timp să utilizați?
Este timpul să vorbiți despre acele opțiuni de formatare a timpului: ați fi putut alege 13:30, 13:30:55 sau 37:30:55. În capturile de ecran de mai sus, am folosit 13:30:55. Problema cu acest lucru apare la sfârșitul săptămânii. Când totalizați orele zecimale în coloana H, aveți 53 de ore facturabile. Când totalizați timpii în G, primiți cinci ore. Ce se întâmplă?
Ambele sunt corecte, dacă înțelegeți formatul numeric pe care l-ați selectat. Spuneți că intrați =NOW()într-o celulă. Puteți formata celula pentru a afișa data și ora, doar data sau doar ora. Excel nu are nicio problemă să lase porțiunea din 29 mai din 29 mai 08:15:34 AM dacă îi spui să facă asta. Răspunsul din celula G57 este „2 zile și cinci ore”. Dar când ați ales 13:37:55 ca format de oră, îi spuneați Excel să nu lase numărul de zile și să vă arate doar orele, minutele și secundele.
Dacă convertiți coloana G pentru a utiliza formatul 37:30:55, atunci veți obține numărul corect de ore:
Titlul de deasupra acelei imagini surprinde mica problemă cu formatul 37:30:55: afișează secunde, ceea ce pare inutile, deoarece ne rotunjim la cele mai apropiate cincisprezece minute. După ce ați ales 37:30:55, faceți clic pe categoria Personalizat. Puteți vedea codul de formatare a numărului (h):mm:ss;@utilizat. Parantezele pătrate din jurul H este partea importantă.
Pentru a afișa orele dvs. în ore și minute, editați formatul numărului pentru a elimina: ss.
Urmăriți videoclipul
Videoclipul de astăzi conține un sfat bonus la final pentru formatarea zecimalelor ca o fracțiune.
Transcriere video
Aflați Excel din Podcast, episodul 2210: Rotunjiți până la următoarea oră.
Hei, bine ai revenit pe netcast, eu sunt Bill Jelen. Astăzi, o altă întrebare de la seminarul meu din Atlanta Power Excel. Cineva făcea o foaie de calcul pentru facturare și voiau să se rotunjească până la următorul sfert de oră, nu? Deci, pentru fiecare sarcină de aici, ei au un client, ora de început și ora de sfârșit și pentru a calcula timpul scurs - asta este simplu, dacă acestea sunt vremuri reale sau dacă sunt doar = E2-D2. Deci, asta spune 55 de minute, dar o spune într-un mod foarte ciudat - „12:55 AM”. Vrem să mergem mai departe și să formatăm acest lucru. Alege Timpul și, hei, uite, aici sunt câteva alegeri diferite: 13:30; 13:30:55, care ar arăta secundele; și apoi 37:30:55. Acum, deocamdată, voi alege 13:30, dar vom reveni mai târziu și vom examina din nou această alegere. Deci, aleg OK și fac dublu clic pentru a-l copia,și vedeți că face doar calculele - deci aici, 25 de minute. Dar persoana care a pus această întrebare - firma pentru care lucrează - vrea să se rotunjească până la următorul sfert de oră.
Bine, deci, un sfert de oră. Să ne gândim la vremurile din Excel. Amiaza este 0,5. Dacă formatez asta ca oră - 12:00:00 PM. Și dacă fac 0,25, asta va fi 6:00:00 AM. Bine, deci modul în care funcționează este că o zi întreagă este numărul 1.0 și, așa că, dacă am vrea să ne dăm seama de 15 minute, ar fi … există 24 de ore x 4 ore sfert, așa că ar trebui să punem = 1/96 acolo și asta ne dă 15 minute. Acum, 1/96 - pot să fac asta matematică în cap: 24x4; 1/96 - este cu siguranță mai ușor să vă amintiți 1/96 decât să vă amintiți această zecimală: 0,01041666667. Bine, așa că a fost mai ușor să folosim 1/96 acolo și așa vom folosi o funcție numită CEILING.
TAVAN. Vezi, avem de ales între CEILING și CEILING.MATH pentru numere pozitive identice. Este mai scurt să tastați CEILING, așa că voi folosi CEILING. Și, rotunjit, există 1/96 dintr-o zi - adică 15 minute - și acesta este răspunsul corect, într-un format greșit. Vom copia aceste formate peste - Ctrl + C, apoi Alt + E + S + T pentru a lipi formate speciale - și cele 55 de minute rundă până la următoarea oră. Faceți dublu clic pentru a copia asta și, vedeți, cele 35 de minute rundă până la următoarele 45; 25 de runde până la următoarele 30. Se pare că funcționează, deși persoana care a pus această întrebare - se rotunjește până la următoarea 15. Poate chiar și compania dvs. folosește ceva diferit.
Bine, deci pentru a rotunji la cel mai apropiat sfert de oră, folosim 1/96. Dar pentru a rotunji la cele mai apropiate 5 minute - există 24 de ore, există 12 perioade de 5 minute în fiecare oră, deci 12x4 = 288. Folosiți 1/288 pentru a rotunji la cele mai apropiate 5 minute. Dacă rotunjiți la cele mai apropiate 6 minute - există 10 perioade de 6 minute x 24 de ore; rotunjiți la cea mai apropiată 1/240. Pentru a rotunji la cel mai apropiat minut, alegeți la cel mai apropiat 1/1440.
Bine, deci poți face acest lucru și avem și de unde alege. Plafonul se va rotunji întotdeauna, deci 16 minute - să mergem la 1/96 acolo - 16 minute să se rotunjească până la 30 și, deși sunt sigur că avocatul meu consideră că este o modalitate excelentă de a factura, este nu prea corect pentru mine - clientul. În regulă, așa că știi CEILING se va rotunji. Avocatul meu nu va folosi niciodată acest lucru: acest ETAJ se va rotunji întotdeauna în jos. Dar, probabil ceea ce este cel mai corect lucru aici este să folosiți MROUND. Deci, = MROUND din timp până la cel mai apropiat 1/96, iar asta va fi rotunjit în jos pentru totul până la aproximativ 22,5 minute; și apoi, după aceea, se va rotunji. Bine, așa că se apropie de orice este cel mai aproape de el. Deci, trebuie să alegeți dacă veți folosi FLOOR, MROUND sau CEILING și apoi care va fi divizorul dvs. - dacă este 'Va fi la cele mai apropiate 5 minute, cel mai apropiat minut sau, știi, orice. Bine, așa că ne întoarcem.
Acum, un sfat bonus aici - acesta a fost Larry în acel seminar - Larry spune: „Hei, uite, nu este chiar bine să arătăm timp facturabil în astfel de vremuri; ne dorim cu adevărat să ne convertim la ore, pentru că te duci cu adevărat să facturați la un anumit tarif - știți, 35 USD / oră, 100 USD / oră, 500 USD / oră, indiferent de tariful dvs. - și trebuie să putem înmulți orele x tarifele. Deci, ar fi bine să să putem converti acele ore în timp zecimal. Deci, luăm acest timp în G2 și îl înmulțim cu 24, iar asta ne va da un număr zecimal de ore, dar va fi într-un format greșit. Voi alege un format de oră în acest caz, când nu ar trebui să fie deloc un format de oră; ar trebui să fie doar în format Număr. Și asigurați-vă că includeți cel puțin 2 zecimale. Faceți dublu clic pentru a copia acest lucru și obținem vremurile noastre ".
În regulă, revenind la întrebarea noastră aici despre „Ce format de număr ar trebui să folosim?”, Acesta este un loc foarte bun pentru a vorbi despre „De ce?” Dacă alegem toate aceste numere, aici, vom vedea că suma este de 53 de ore, iar dacă vin aici și pun o funcție SUM, primim 53 de ore. Este perfect. Dar, dacă folosesc funcția SUM sub o coloană de timp și fac Alt + = sau AUTOSUM, îmi oferă 5 ore … 5 ore. Care este corect - 53 sau 5?
Ei bine, se dovedește într-un sens ciudat, amândoi au dreptate. Iată de ce amândoi au dreptate. Voi introduce o funcție numită = ACUM. Veți vedea că de fapt înregistrez acest lucru pe 9 mai, deci, acum 20 de zile, și este ora 7:08 și cu = ACUM, uneori vreau să arăt data și ora, dar alteori, vreau doar pentru a arăta data. Deci, pot face = ACUM dă-mi data curentă sau, pot face = ACUM dă-mi ora curentă. Astfel, funcțiile Număr sunt fericite fie să trunchieze timpul liber, fie să elimine data oprită. Iar aceasta pe care am ales-o aici jos, ora 13:30, spune: „Hei, nu vreau să văd numărul de zile; arată-mi doar ora”. Da, asta este problema - deoarece aceasta este într-adevăr 2 zile și 5 ore - 2 zile sunt 48 de ore - 2 zile și 5 ore. Iar noi i-am spus … i-am spus lui Excel: "Hei, nu vă deranjați să-mi arătați zilele. "Ei bine, nu știam că spunem asta Excel, dar asta îi spuneam lui Excel când am ales-o pe aceasta sau pe aceasta. coborâți aici până la 37:30: 55. Acesta este cel care ne va arăta numărul complet de ore și vom primi răspunsul corect de 53 de ore.
Dar, pare chiar o prostie să arătăm acele secunde în plus când rotunjim la 15 minute. Deci, să revenim la asta. După ce alegeți 37:30:55 pentru a afla formatul numărului din spatele acestuia, faceți clic pe Personalizat. Veți vedea că lucrul care îl face să funcționeze sunt parantezele pătrate din jurul orei. Dar, nu avem nevoie de: ss. Deci, acum putem edita acel format personalizat, astfel încât avem ore și minute. Nu trebuie să înțelegem astăzi ce înseamnă; @. Se spune că, pentru text, arată doar textul; dar, lasă acea parte acolo, chiar dacă nu o înțelegi. Faceți clic pe OK și acum avem timpul nostru în ore și minute, lăsând în afara secundele. Dar, totalurile funcționează. Într-adevăr, într-adevăr subtil, că 13:30 și că 13:30:55. Știi, nu scrie „Hei, îi spui Excel să ignore data … zilele întregi”. Dar,tu esti.
Lucruri precum Preluarea timpului sunt în această carte: LIVe: Cele mai mari 54 de sfaturi Excel din toate timpurile. Faceți clic pe „Eu” în colțul din dreapta sus pentru a o verifica.
Părea o întrebare atât de ușoară; Nu-mi vine să cred că suntem deja în 7 minute. Însă, o companie vrea să factureze în sfert de oră și doresc întotdeauna să se rotunjească până la următorul sfert de oră. Deci, 24 de ore pe zi, 24x4 = 96; 15 minute = 1/96. Deci, utilizați CEILING (A2,1 / 96) pentru a rotunji până la următorul sfert de oră. Utilizați MROUND (A2,1 / 96) pentru a rotunji la cel mai apropiat sfert de oră, apoi utilizați FLOOR (A2,1 / 96) pentru a rotunji la sfertul de oră inferior - întotdeauna se întoarce. Există 24 de ore pe zi, deci puteți face 1/288 pentru a ajunge la perioade de 5 minute, 1/240 pentru a ajunge la perioade de 6 minute, 1/1440 pentru a rotunji până la cel mai apropiat minut. Și apoi, sfatul bonusului la sfârșit (ce format de număr) - doriți întotdeauna să reveniți la acel 37:30:55 dacă doriți să arătați ore mai mari de 24 de ore.
To download the workbook from today's video, visit that URL down there in the Youtube description.
I want to thank you for stopping by; we'll see you next time for another netcast from.
Hey! Thanks for hanging along. But, just one more bonus tip here (that I'm not even going to bother to mention). What I started out with here-- let's clear this whole thing-- a little formula that came over and pointed to one of these decimals-- so, the nearest quarter hour. But, I wanted to show that not as the 0.010417. It's awesome-- in the Number Formatting, there's actually something called Fractions, and if I choose "Up to two digits," I'm going to get 1/96; but, I need to handle that 1/288, so I choose "Up to three digits," and it still shows me 1/96; but, then when I convert over to this one -- the 1/288 -- it will show it correctly. Now, I guess I should have done "four digits" to handle the 1/1440. What's that going to do? That's going to give me… But, if I go into the Number Format, and go to Custom, and just add an extra question mark there, then we'll get to the nearest 1/1440 of a fraction. Number Format-- cool, cool thing.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: round-to-quarter-hour.xlsx
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Prietenii nu le permit prietenilor să folosească INDIRECT”
Jordan Goldmeier
