Nog een vraag van mijn Atlanta Power Excel-seminar:
Hoe kunt u de factureerbare tijd afronden tot het volgende kwartier?
Stel dat u een logboek bijhoudt van uw factureerbare tijd. Je hebt een begintijd en een eindtijd. Een formule zoals =E2-D2berekent de verstreken tijd.
Het resultaat in F2 is 55 minuten. Maar de standaardopmaak toont 12:55 uur. Kies de hele kolom. Druk op Ctrl + 1 om cellen op te maken. Selecteer de categorie Tijd. Welke van deze moet je gebruiken? Kies voorlopig 13:30:55, maar u zult later het probleem met deze selectie zien.
Uw bedrijfsbeleid is om alle facturen naar het volgende kwartier af te ronden. Doe deze berekening: er zijn 24 uur in een dag. Er zijn 4 kwartieren in een uur. Vermenigvuldig 24 * 4 om 96 te krijgen. Dat betekent dat een kwartier gelijk is aan 1/96 van een dag.
Gebruik de formule =CEILING(F2,1/96)om de verstreken tijd in F af te ronden naar het volgende kwartier. In Excel's methode om tijd op te slaan, is middag 0,5. 06.00 uur is 0.25 uur. 12:15 uur is 1/96 of 0,010417. Het is gemakkelijker om 1/96 te onthouden dan 0,010417.
De resultaten zijn hieronder weergegeven. Merk op hoe de 35 minuten worden afgerond op 45 minuten. Dit lijkt de formule te zijn die mijn advocaat gebruikt … haha, dat is maar een grapje Esquire Dewey! Klaag me niet aan! Ik weet dat ze gebruiken =CEILING(F2,4)…
Hier pauzeren… dit artikel wordt afgerond naar het volgende kwartier, want dat is de methode die Larry in mijn seminar gebruikt. Grote kans dat uw bedrijf op iets anders afrondt. De onderstaande afbeelding laat zien hoe je afrondt naar vijf minuten of 1 minuut. U kunt dezelfde logica gebruiken om af te ronden naar de dichtstbijzijnde 6 minuten (1/240) of 12 minuten (1/120).
Ook - een vriendelijke suggestie die het verschil laat zien tussen FLOOR, MROUND en CEILING. De FLOOR-functie in B9: B22 rondt altijd naar beneden af. De MROUND in C9: C22 wordt afgerond op de dichtstbijzijnde. Het PLAFOND in D9: D22 rondt af.
Opmerking
De Excel-tooltip zal voorstellen dat u FLOOR.MATH of CEILING.MATH gebruikt in plaats van FLOOR of CEILING. De nieuwe ".Math" -versies van deze functies gaan anders om met negatieve getallen. Omdat tijd in Excel nooit negatief kan zijn, is het prima om de gewone VLOER of PLAFOND te gebruiken.
Verander de breuk van 1/96 in 1/288 naar rond in stappen van vijf minuten.
Tijden omzetten in uren
Nog een suggestie van Larry: zet de tijden om in echte uren. Op die manier werkt de wiskunde wanneer u vermenigvuldigt met het factureringstarief per uur. Neem de afgeronde verstreken tijd en vermenigvuldig deze met 24 om een decimaal aantal uren te krijgen. Zorg ervoor dat u het resultaat opmaakt als een getal met twee decimalen en niet als een tijd.
Welk tijdformaat te gebruiken?
Het is tijd om over die tijdopmaakkeuzes te praten: je had 13:30, 13:30:55 of 37:30:55 kunnen kiezen. In de bovenstaande screenshots heb ik 13:30:55 gebruikt. Het probleem hiermee doet zich aan het einde van de week voor. Als u de decimale uren in kolom H optelt, heeft u 53 factureerbare uren. Als je de tijden in G optelt, krijg je vijf uur. Wat is er aan de hand?
Beide zijn correct, als u de getalnotatie begrijpt die u hebt geselecteerd. Zeg dat je =NOW()een cel binnengaat . U kunt de cel opmaken om datum en tijd, alleen de datum of alleen de tijd weer te geven. Excel heeft geen probleem om het gedeelte van 29 mei van 29 mei 08:15:34 uur te verlaten als u het zegt dat te doen. Het antwoord in cel G57 is "2 dagen en vijf uur". Maar toen u 13:37:55 als tijdnotatie koos, vertelde u Excel om het aantal dagen weg te laten en alleen de uren, minuten en seconden te tonen.
Als u kolom G converteert naar het formaat 37:30:55, krijgt u het juiste aantal uren:
Het onderschrift boven die afbeelding geeft het kleine probleem weer met het 37:30:55-formaat: het toont seconden, wat overbodig lijkt omdat we afronden op het dichtstbijzijnde kwartier. Nadat u 37:30:55 hebt gekozen, klikt u op de categorie Aangepast. U kunt zien dat de nummeropmaakcode (h):mm:ss;@wordt gebruikt. De vierkante haken rond de H zijn het belangrijkste onderdeel.
Om uw tijden in uren en minuten weer te geven, bewerkt u de getalnotatie om de: ss te verwijderen.
Bekijk video
De video van vandaag bevat aan het einde een bonustip voor het opmaken van decimalen als breuken.
Videotranscriptie
Leer Excel van Podcast, aflevering 2210: Afronden naar het volgende kwartier.
Hé, welkom terug bij netcast, ik ben Bill Jelen. Vandaag nog een vraag van mijn Atlanta Power Excel-seminar. Iemand was bezig met een factureringsspreadsheet en ze wilden naar het volgende kwartier afronden, toch? Dus voor elke taak hier hebben ze een cliënt, starttijd en eindtijd, en om de verstreken tijd te berekenen - dat is eenvoudig, als dat echte tijden zijn, of als ze gewoon = E2-D2 zijn. Dus dat zegt 55 minuten, maar het zegt het op een heel rare manier: "12:55 uur." We willen doorgaan en dit formatteren. Kies Tijd, en kijk, er zijn een paar verschillende keuzes hier: 13:30; 13:30:55, die de seconden zou laten zien; en dan 37:30:55. Nu, voor nu, ga ik 13:30 kiezen, maar we zullen later terugcirkelen en die keuze opnieuw onderzoeken. Dus ik kies OK en dubbelklik om het te kopiëren,en je ziet dat het gewoon de wiskunde aan het doen is - dus hier, 25 minuten. Maar de persoon die deze vraag stelde - de firma waarvoor ze werken - wil naar het volgende kwartier afronden.
Oké, dus een kwartier. Laten we eens nadenken over tijden in Excel. De middag is 0,5 uur. Als ik dat opmaak als een tijd … 12.00 uur. En als ik 0.25 doe, is dat 06.00 uur. Oké, dus de manier waarop dit werkt, is dat een volledige dag het getal 1.0 is, en dus, als we 15 minuten wilden berekenen, zou het zijn-- er zijn 24 uur x 4 kwartier, dus we zouden = 1/96 daar, en dat geeft ons 15 minuten. Nu, 1/96 - ik kan die wiskunde in mijn hoofd doen: 24x4; 1/96 - het is zeker gemakkelijker om 1/96 te onthouden dan om dit decimaal te onthouden: 0,01041666667. Oké, dus het was gemakkelijker om 1/96 daar te gebruiken, en dus gaan we een functie gebruiken genaamd CEILING.
PLAFOND. Kijk, we hebben de keuze tussen CEILING en CEILING.MATH voor positieve getallen die identiek zijn. Het is korter om CEILING te typen, dus ik ga CEILING gebruiken. En afgerond, er is 1/96 van een dag - dat is 15 minuten - en dat is het juiste antwoord, in het verkeerde formaat. We gaan deze formaten kopiëren - Ctrl + C, en dan Alt + E + S + T om speciale formaten te plakken - en de 55 minuten worden afgerond naar het volgende uur. Dubbelklik om dat naar beneden te kopiëren, en zie, de 35 minuten worden afgerond naar de volgende 45; 25 rondt af naar de volgende 30. Het lijkt te werken, hoewel de persoon die deze vraag stelde naar boven afronden naar de volgende 15. Misschien gebruikt zelfs uw bedrijf iets anders.
Oké, dus om af te ronden naar het dichtstbijzijnde kwartier, gebruiken we 1/96. Maar om af te ronden op de dichtstbijzijnde 5 minuten - er zijn 24 uur, er zijn 12 perioden van 5 minuten in elk uur, dus 12x4 = 288. Gebruik 1/288 om af te ronden naar de dichtstbijzijnde 5 minuten. Als je afrondt op de dichtstbijzijnde 6 minuten - er zijn 10 periodes van 6 minuten x 24 uur; je rondt af op de dichtstbijzijnde 1/240. Om naar de dichtstbijzijnde minuut af te ronden, kiest u naar de dichtstbijzijnde 1/1440.
Oké, dus je kunt dit doen, en we hebben ook een keuze. CEILING wordt altijd afgerond, dus 16 minuten - laten we daar naar 1/96 gaan - 16 minuten worden afgerond tot 30, en hoewel ik zeker weet dat mijn advocaat denkt dat dit een geweldige manier is om te factureren, is het niet echt eerlijk voor mij - de klant. Oké, dus je weet dat CEILING naar boven zal worden afgerond. Mijn advocaat zal dit nooit gebruiken: deze VERDIEPING zal altijd naar beneden worden afgerond. Maar waarschijnlijk is het eerlijkste hier om MROUND te gebruiken. Dus = MROUND van de tijd naar het dichtstbijzijnde 1/96, en dat wordt voor alles naar beneden afgerond tot ongeveer 22,5 minuten; en dan, daarna, wordt het afgerond. Oké, dus het wordt afgerond naar datgene waar het het dichtst bij staat. Dus je moet kiezen of je FLOOR, MROUND of CEILING gaat gebruiken, en wat je deler zal zijn - of het nu 's gaat naar de dichtstbijzijnde 5 minuten, de dichtstbijzijnde minuut, of, weet je, wat dan ook. Oké, dus daar zijn we aan het afronden.
Nu, een bonustip hier - dit was Larry in dat seminar - Larry zegt: "Hé, kijk, het is niet echt goed om in dergelijke tijden factureerbare tijd weer te geven; we willen echt converteren naar uren, want je gaat echt om tegen een bepaald tarief te factureren - weet u, $ 35 / uur, $ 100 / uur, $ 500 / uur, wat uw tarief ook is - en we moeten het aantal uren x de tarieven kunnen vermenigvuldigen. Het zou dus leuk zijn om in staat zijn om die uren om te rekenen naar een decimale tijd. Dus nemen we die tijd in G2 en vermenigvuldigen die met 24, en dat geeft ons een decimaal aantal uren, maar het zal de verkeerde notatie hebben. ga in dit geval een tijdnotatie kiezen, terwijl het helemaal geen tijdnotatie zou moeten zijn; het zou alleen in getalnotatie moeten zijn. En zorg ervoor dat u ten minste 2 decimalen opneemt. Dubbelklik om dat naar beneden te kopiëren, en we krijgen onze tijden. "
Oké, nu we teruggaan naar onze vraag "Welk getalformaat moeten we gebruiken?", Dit is echt een goede plek om te praten over "Waarom?". Als we al deze getallen hier kiezen, zien we dat de som 53 uur is, en als ik hier kom en een SOM-functie installeer, krijgen we 53 uur. Dat is perfect. Maar als ik de SOM-functie onder een tijdkolom gebruik, en ik doe Alt + = of de AUTOSUM, dan heb ik 5 uur… 5 uur. Welke is juist - 53 of 5?
Nou, het blijkt op een rare manier dat ze allebei gelijk hebben. Maar hier is waarom ze allebei gelijk hebben. Ik ga een functie invoeren met de naam = NU. Je zult zien dat ik dit daadwerkelijk opneem op 9 mei, dus 20 dagen geleden, en het is 7:08 uur. En met = NU, soms wil ik de datum en de tijd laten zien, maar andere keren wil ik gewoon om de datum weer te geven. Dus ik kan maken = NU geef me de huidige datum, of, ik kan maken = NU geef me de huidige tijd. En dus zijn de nummerfuncties blij om de vrije tijd in te korten of de datum te verwijderen. En deze die we hier kozen, de 13:30, zegt: "Hé, ik wil het aantal dagen niet zien; laat me gewoon de tijd zien." Ja, dat is het probleem - want dit is echt 2 dagen en 5 uur - 2 dagen is 48 uur - 2 dagen en 5 uur. En we vertelden het - we zeiden tegen Excel: "Hé, doe niet de moeite om me de dagen te laten zien. "Nou, we wisten niet dat we dat tegen Excel vertelden, maar dat is wat we tegen Excel vertelden toen we deze of deze kozen. Oké, dus je wordt gedwongen om kom hier naar 37:30:55, dat is degene die ons het volledige aantal uren laat zien en we krijgen het juiste antwoord van 53 uur.
Maar het lijkt me echt gek om die extra seconden te laten zien als we afronden op 15 minuten. Laten we daar dus op terugkomen. Nadat u 37:30:55 hebt gekozen om het getalformaat erachter te leren, klikt u op Aangepast. Je zult zien dat het ding dat ervoor zorgt dat het werkt, de vierkante haakjes rond het uur zijn. Maar we hebben de: ss niet nodig. Dus nu kunnen we dat aangepaste formaat bewerken, en dus hebben we uren en minuten. We hoeven vandaag niet te begrijpen wat de; @ betekent. Het zegt dat, voor tekst, gewoon de tekst laat zien; maar laat dat deel gewoon daar, zelfs als u het niet begrijpt. Klik op OK en we hebben nu onze tijd in uren en minuten, zonder de seconden. Maar de totalen werken. Echt heel subtiel, dat 13:30 en dat 13:30:55. Weet je, er staat niet: "Hé, je vertelt Excel om de datum … de volledige dagen te negeren." Maar,je bent.
Dingen zoals Tijd nemen voor wiskunde staan in dit boek: LIVe: de 54 beste Excel-tips aller tijden. Klik op die "I" in de rechterbovenhoek om het te bekijken.
Dit klonk als zo'n gemakkelijke vraag; Ik kan niet geloven dat we al 7 minuten over zijn. Maar een bedrijf wil factureren in kwarturen en ze willen altijd afronden naar het volgende kwartier. Dus 24 uur per dag, 24x4 = 96; 15 minuten = 1/96. Gebruik dus CEILING (A2,1 / 96) om af te ronden naar het eerstvolgende hogere kwartier. Gebruik MROUND (A2,1 / 96) om af te ronden naar het dichtstbijzijnde kwartier en gebruik dan FLOOR (A2,1 / 96) om af te ronden naar het lagere kwartier - gaat altijd terug. Er zit 24 uur in een dag, dus je kunt 1/288 doen om naar periodes van 5 minuten te gaan, 1/240 om naar periodes van 6 minuten te gaan, 1/1440 om op de dichtstbijzijnde minuut af te ronden. En dan, de bonustip daar aan het einde (welk getalformaat) - je wilt altijd teruggaan naar dat 37:30:55 als je uren van meer dan 24 uur wilt laten zien.
Om de werkmap van de video van vandaag te downloaden, bezoekt u die URL daar beneden in de YouTube-beschrijving.
Ik wil je bedanken voor het langskomen; we zien je de volgende keer voor nog een netcast van.
Hallo! Bedankt voor het langskomen. Maar nog een bonustip hier (die ik niet eens ga noemen). Waar ik hier mee begon - laten we dit hele ding duidelijk maken - een kleine formule die naar een van deze decimalen wees - dus het dichtstbijzijnde kwartier. Maar ik wilde dat niet laten zien als de 0.010417. Het is geweldig - in de nummeropmaak is er eigenlijk iets dat breuken wordt genoemd, en als ik "Maximaal twee cijfers" kies, krijg ik 1/96; maar ik moet die 1/288 afhandelen, dus ik kies "Maximaal drie cijfers", en het laat me nog steeds 1/96 zien; maar als ik dan naar deze converteer - de 1/288 - zal het correct worden weergegeven. Nu, ik denk dat ik "vier cijfers" had moeten doen om de 1/1440 af te handelen. Wat gaat dat doen? Dat geeft me … Maar,als ik naar het getalformaat ga en naar Aangepast ga en daar gewoon een extra vraagteken toevoeg, dan komen we bij de dichtstbijzijnde 1/1440 van een breuk. Getalnotatie - cool, cool ding.
Download Excel-bestand
Om het Excel-bestand te downloaden: round-to-quarter-hour.xlsx
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Vrienden laten vrienden INDIRECT niet gebruiken"
Jordan Goldmeier
