En annan fråga från mitt Atlanta Power Excel-seminarium:
Hur kan du runda fakturerbar tid fram till nästa kvartal?
Säg att du registrerar din fakturerbara tid. Du har en starttid och en sluttid. En formel som =E2-D2beräknar förfluten tid.
Resultatet i F2 är 55 minuter. Men standardformateringen visar 12:55. Välj hela kolumnen. Tryck på Ctrl + 1 för att formatera celler. Välj tidskategori. Vilka av dessa ska du använda? För närvarande väljer du 13:30:55, men du kommer senare att se problemet med detta val.
Ditt företags policy är att avrunda alla faktureringar fram till nästa kvartal. Gör denna beräkning: Det finns 24 timmar om dagen. Det finns fyra kvartals timmar på en timme. Multiplicera 24 * 4 för att få 96. Det betyder att en kvarts timme motsvarar 1/96 av en dag.
Använd formeln för =CEILING(F2,1/96)att avrunda den förflutna tiden i F fram till nästa kvartal. I Excels metod för att lagra tid är Noon 0,5. 06:00 är 0,25. 12:15 är 1/96 eller 0,010417. Det är lättare att komma ihåg 1/96 än 0,010417.
Resultaten visas nedan. Lägg märke till hur 35 minuter rundas upp till 45 minuter. Det verkar vara den formel som min advokat använder … haha, det är bara ett skämt Esquire Dewey! Stäm inte mig! Jag vet att de använder =CEILING(F2,4)…
Pausar här … den här artikeln avrundas till nästa kvartal eftersom det är den metod som Larry använder i mitt seminarium. Det finns en god chans att ditt företag rundar till något annat. Bilden nedan visar hur man avrundar till fem minuter eller 1 minut. Du kan använda liknande logik för att avrunda till närmaste 6 minuter (1/240) eller 12 minuter (1/120).
Också - ett mildt förslag som visar skillnaden mellan GOLV, MROUND och TAK. FLOOR-funktionen i B9: B22 avrundas alltid nedåt. MROUND i C9: C22 avrundas till närmaste. TAKET i D9: D22 avrundas.
Notera
Excel-tipset föreslår att du använder FLOOR.MATH eller CEILING.MATH istället för FLOOR eller Ceiling. De nya ".Math" -versionerna av dessa funktioner hanterar negativa tal på olika sätt. Eftersom tid i Excel aldrig kan vara negativ är det bra att använda det vanliga golvet eller taket.
Ändra fraktionen från 1/96 till 1/288 till runda till fem minuters steg.
Konvertera tider till timmar
Ett annat förslag från Larry: Konvertera tiderna till riktiga timmar. På det sättet, när du multiplicerar med faktureringsgraden per timme, kommer matematiken att fungera. Ta den avrundade förflutna tiden och multiplicera med 24 för att få ett decimalt antal timmar. Se till att formatera resultatet som ett tal med två decimaler och inte en tid.
Vilket tidsformat ska du använda?
Det är dags att prata om dessa tidsformateringsval: Du kunde ha valt 13:30, 13:30:55 eller 37:30:55. I skärmdumparna ovan har jag använt 13:30:55. Problemet med detta dyker upp i slutet av veckan. När du summerar decimaltimmarna i kolumn H har du 53 fakturerbara timmar. När du summerar tiderna i G får du fem timmar. Vad händer?
Båda är korrekta om du förstår det nummerformat du valde. Säg att du går in =NOW()i en cell. Du kan formatera cellen så att den visar datum och tid, bara datum eller bara tid. Excel har inga problem att lämna 29 maj-delen av 29 maj 08:15:34 om du säger att den ska göra det. Svaret i cell G57 är "2 dagar och fem timmar". Men när du valde 13:37:55 som tidsformat, sa du till Excel att sluta antalet dagar och bara visa timmar, minuter och sekunder.
Om du konverterar kolumn G till att använda formatet 37:30:55 får du rätt antal timmar:
Bildtexten ovanför den bilden fångar det lilla problemet med formatet 37:30:55: det visar sekunder, vilket verkar onödigt eftersom vi avrundar till närmaste femton minuter. När du har valt 37:30:55 klickar du på kategorin Anpassad. Du kan se att nummerformateringskoden (h):mm:ss;@används. De hakparenteserna runt H är den viktiga delen.
Om du vill visa dina tider i timmar och minuter redigerar du nummerformatet för att ta bort: ss.
Kolla på video
Dagens video innehåller ett bonustips i slutet för att formatera decimaler som en bråkdel.
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2210: Runda upp till nästa kvarts timme.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Idag en ny fråga från mitt Atlanta Power Excel-seminarium. Någon gjorde ett faktureringsark, och de ville runda upp till nästa kvartal, eller hur? Så för varje uppgift här har de en klient, starttid och sluttid och för att beräkna förfluten tid - det är enkelt, om det är verkliga tider, eller om de bara är = E2-D2. Så det säger 55 minuter, men det säger det på ett riktigt konstigt sätt - "12:55." Vi vill fortsätta och formatera detta. Välj tid, och hej, titta, det finns några olika val här: 13:30; 13:30:55, vilket skulle visa sekunderna; och sedan 37:30:55. Nu, just nu, ska jag välja 13:30, men vi kommer att ringa tillbaka senare och ompröva det valet. Så jag väljer OK och dubbelklickar för att kopiera ner det,och du ser att det bara gör matte - så här, 25 minuter. Men den som ställde den här frågan - företaget de jobbar för - de vill avrunda till nästa kvartal.
Okej, så, kvart timme. Låt oss tänka på tider i Excel. Middag är 0,5. Om jag formaterar det som en tid-- 12:00:00 PM. Och om jag gör 0,25 blir det 06:00:00. Okej, så hur det här fungerar är att en hel dag är siffran 1.0, och om vi ville ta reda på 15 minuter skulle det vara - det finns 24 timmar x 4 kvart timmar, så vi måste sätta = 1/96 där inne, och det ger oss 15 minuter. Nu, 1/96 - Jag kan på så sätt göra den matematiken i mitt huvud: 24x4; 1/96 - det är verkligen lättare att komma ihåg 1/96 än att komma ihåg detta decimal: 0,01041666667. Okej, så det var lättare att använda 1/96 där, och så kommer vi att använda en funktion som heter CEILING.
TAK. Se, vi har valet mellan CEILING och CEILING.MATH för positiva siffror som är identiska. Det är kortare att skriva CEILING, så jag ska använda CEILING. Och avrundat finns 1/96 av en dag - vilket är 15 minuter - och det är rätt svar, i fel format. Vi kommer att kopiera dessa format över - Ctrl + C och sedan Alt + E + S + T för att klistra in speciella format - och de 55 minuterna rundas upp till nästa timme. Dubbelklicka för att kopiera ner det och se 35 minuter avrundas till nästa 45; 25 omgångar till nästa 30. Det verkar fungera, även om personen som ställde den här frågan - de avrundar till nästa 15. Kanske till och med ditt företag använder något annat.
Okej, så för att avrunda till närmaste kvarts timme använder vi 1/96. Men för att avrunda till närmaste 5 minuter - det finns 24 timmar, det finns 12 5-minutersperioder i varje timme, så 12x4 = 288. Använd 1/288 för att avrunda till närmaste 5 minuter. Om du avrundar till närmaste 6 minuter - finns det 10 6-minutersperioder x 24 timmar; du rundar till närmaste 1/240. För att avrunda till närmaste minut, välj till närmaste 1/1440.
Okej, så du kan göra det, och vi har också ett val. TAK kommer alltid att rundas upp, så 16 minuter - låt oss gå till 1/96 där - 16 minuter kommer att runda upp till 30, och medan jag är säker på att min advokat tycker att det är ett bra sätt att fakturera, det är inte riktigt rättvist mot mig - klienten. Okej, så du vet att CEILING kommer att avrundas. Min advokat kommer aldrig att använda det här: GOLVET kommer alltid att rundas ner. Men förmodligen vad som är det rättvisaste här är att använda MROUND. Så, = tidens MROUND till närmaste 1/96, och det avrundas för allt upp till cirka 22,5 minuter; och sedan, efter det, kommer det att rundas upp. Okej, så det avrundas till vad det är närmast. Så du måste välja om du ska använda GOLV, MROUND eller TAK, och sedan vad din delare kommer att bli - om det 's kommer att vara närmaste 5 minuter, närmaste minut, eller, du vet, vad som helst. Okej, så där avrundas vi.
Nu, ett bonustips här - det här var Larry i det seminariet - Larry säger, "Hej, se, det är inte riktigt bra att visa fakturerbar tid i sådana tider; vi vill verkligen konvertera till timmar för att du verkligen går att fakturera i någon takt - du vet, $ 35 / timme, $ 100 / timme, $ 500 / timme, oavsett vad du är - och vi måste kunna multiplicera timmarna x priserna. Så det skulle vara trevligt att kunna konvertera dessa timmar till en decimaltid. Så vi tar den tiden i G2 och multiplicerar den med 24, och det kommer att ge oss ett decimaltal timmar, men det kommer att vara i fel format. De är kommer att välja ett tidsformat i det här fallet, när det inte borde vara ett tidsformat alls; det borde bara vara i nummerformat. Och se till att ta med minst två decimaler. Dubbelklicka för att kopiera ner det och vi får våra tider. "
Okej, nu kommer vi tillbaka till vår fråga om "Vilket nummerformat ska vi använda?", Det här är ett riktigt bra ställe att prata om "Varför?". Om vi väljer alla dessa siffror här ser vi att summan är 53 timmar, och om jag kommer hit och lägger in en SUM-funktion får vi 53 timmar. Det är perfekt. Men om jag använder SUM-funktionen under en tidskolumn och jag gör Alt + = eller AUTOSUM, ger det mig 5 timmar … 5 timmar. Vilket är rätt - 53 eller 5?
Tja, det visar sig i någon konstig mening, de har båda rätt. Men här är varför de båda har rätt. Jag ska gå in i en funktion som heter = NU. Du ser att jag faktiskt spelar in det här igen den 9 maj, så för 20 dagar sedan, och det är 07:08 och med = NU, ibland vill jag visa datum och tid, men andra gånger vill jag bara för att visa datumet. Så jag kan göra = NU ge mig det aktuella datumet, eller, jag kan göra = NU ge mig aktuell tid. Och så är nummerfunktionerna glada att antingen avkorta ledigheten eller ta bort datumet. Och den här som vi valde här nere, 13:30, säger: "Hej, jag vill inte se antalet dagar, bara visa mig tiden." Ja, det är problemet - för det här är verkligen 2 dagar och 5 timmar - 2 dagar är 48 timmar - 2 dagar och 5 timmar. Och vi sa det - vi sa till Excel, "Hej, bry dig inte om att visa mig dagarna. "Tja, vi visste inte att vi berättade för Excel det, men det var vad vi berättade för Excel när vi valde den här eller den här. Okej, så du tvingas kom hit till 37:30: 55. Det är den som kommer att visa oss hela antalet timmar, och vi får rätt svar på 53 timmar.
Men det verkar väldigt dumt att visa de extra sekunderna när vi avrundar till 15 minuter. Så, låt oss gå tillbaka till det. När du har valt 37:30:55 för att lära dig nummerformatet bakom detta, klicka på Anpassad. Du ser att det som får det att fungera är hakparenteser runt timmen. Men vi behöver inte: ss. Så nu kan vi redigera det anpassade formatet, och så har vi timmar och minuter. Vi behöver inte förstå idag vad; @ betyder. Det säger att, för text, bara visa texten; men lämna bara den delen där, även om du inte förstår det. Klicka på OK så har vi nu vår tid i timmar och minuter och lämnar sekunderna. Men totalen fungerar. Verkligen, riktigt subtilt, den 13:30 och den 13:30:55. Du vet, det står inte "Hej, du säger till Excel att ignorera datumet … hela dagarna." Men,du är.
Saker som Taking Time Math finns i den här boken: LIVe: The 54 Greatest Excel Tips of All Time. Klicka på det "I" i det övre högra hörnet för att kolla in det.
Detta lät som en så lätt fråga; Jag kan inte tro att vi redan är sju minuter kvar. Men ett företag vill fakturera kvartals timmar, och de vill alltid avrunda till nästa kvartals timme. Så, 24 timmar om dagen, 24x4 = 96; 15 minuter = 1/96. Så använd CEILING (A2,1 / 96) för att runda upp till nästa högre kvartal. Använd MROUND (A2,1 / 96) för att avrunda till närmaste kvarts timme, och använd sedan GOLV (A2,1 / 96) för att avrunda till den nedre kvartalet - går alltid tillbaka. Det finns 24 timmar om dagen, så du kan göra 1/288 för att komma till 5-minutersperioder, 1/240 för att komma till 6-minutersperioder, 1/1440 för att runda till närmaste minut. Och sedan, bonustipsen där i slutet (vilket nummerformat) - du vill alltid gå tillbaka till det 37:30:55 om du ska visa timmar som överstiger 24 timmar.
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.
Ladda ner Excel-fil
För att ladda ner Excel-filen: round-to-quarter-hour.xlsx
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Vänner låter inte vänner använda INDIRECT"
Jordan Goldmeier
