Et annet spørsmål fra Atlanta Power Excel-seminaret mitt:
Hvordan kan du runde fakturerbar tid frem til neste kvart time?
Si at du fører en logg over fakturerbar tid. Du har starttid og sluttid. En formel som =E2-D2beregner forløpt tid.
Resultatet i F2 er 55 minutter. Men standardformateringen viser 12:55. Velg hele kolonnen. Trykk Ctrl + 1 for å formatere celler. Velg Tidskategori. Hvilke av disse bør du bruke? Foreløpig velger du 13:30:55, men du vil senere se problemet med dette valget.
Bedriftens policy er å runde alle faktureringer opp til neste kvart time. Gjør denne beregningen: Det er 24 timer om dagen. Det er 4 kvart timer på en time. Multipliser 24 * 4 for å få 96. Det betyr at en kvart time tilsvarer 1/96 av en dag.
Bruk formelen =CEILING(F2,1/96)til å runde forløpt tid i F opp til neste kvart time. I Excels metode for lagring av tid er Noon 0,5. 06:00 er 0,25. 12:15 er 1/96 eller 0,010417. Det er lettere å huske 1/96 enn 0,010417.
Resultatene er vist nedenfor. Legg merke til hvordan de 35 minuttene runder opptil 45 minutter. Dette ser ut til å være formelen advokaten min bruker … haha, det er bare en vits Esquire Dewey! Ikke saksøk meg! Jeg vet at de bruker =CEILING(F2,4)…
Pauser her … denne artikkelen avrunder til neste kvart time fordi det er metoden Larry i seminaret mitt bruker. Det er en god sjanse for at ditt selskap runder til noe annet. Bildet nedenfor viser hvordan man avrunder til fem minutter eller 1 minutt. Du kan bruke lignende logikk for å avrunde til nærmeste 6 minutter (1/240) eller 12 minutter (1/120).
Også - et mildt forslag som viser forskjellen mellom GULV, MROUND og TAK. FLOOR-funksjonen i B9: B22 avrunder alltid nedover. MROUND i C9: C22 avrunder til nærmeste. TAKET i D9: D22 runder opp.
Merk
Excel-verktøytips vil foreslå at du bruker FLOOR.MATH eller CEILING.MATH i stedet for FLOOR eller CEILING. De nye ".Math" -versjonene av disse funksjonene håndterer negative tall forskjellig. Fordi tid i Excel aldri kan være negativ, er det greit å bruke det vanlige gulvet eller taket.
Endre brøkdelen fra 1/96 til 1/288 til trinn til runde til fem minutter.
Konvertere tider til timer
Et annet forslag fra Larry: Konverter tidene til virkelige timer. På den måten, når du multipliserer med faktureringsgraden per time, vil matematikken fungere. Ta avrundet forløpt tid og multipliser med 24 for å få et desimalt antall timer. Sørg for å formatere resultatet som et tall med to desimaler og ikke en tid.
Hvilket tidsformat skal du bruke?
Det er på tide å snakke om disse tidsformateringsvalgene: Du kunne ha valgt 13:30, 13:30:55 eller 37:30:55. I skjermbildene ovenfor har jeg brukt 13:30:55. Problemet med dette dukker opp på slutten av uken. Når du summerer desimaltimene i kolonne H, har du 53 fakturerbare timer. Når du summerer tidene i G, får du fem timer. Hva skjer?
Begge er korrekte, hvis du forstår nummerformatet du valgte. Si at du går inn =NOW()i en celle. Du kan formatere cellen slik at den viser dato og tid, bare dato eller bare tid. Excel har ingen problemer med å slutte 29. mai-delen av 29. mai 08:15:34, hvis du ber den gjøre det. Svaret i celle G57 er "2 dager og fem timer". Men da du valgte 13:37:55 som tidsformat, ba du Excel om å slutte antall dager og bare vise deg timer, minutter og sekunder.
Hvis du konverterer kolonne G til å bruke formatet 37:30:55, får du riktig antall timer:
Bildeteksten over det bildet fanger det lille problemet med formatet 37:30:55: det viser sekunder, noe som virker unødvendig siden vi avrunder til nærmeste femten minutter. Etter å ha valgt 37:30:55, klikk på kategorien Egendefinert. Du kan se nummerformateringskoden for (h):mm:ss;@brukes. De firkantede parentesene rundt H er den viktige delen.
For å vise tidene dine i timer og minutter, rediger du tallformatet for å fjerne: ss.
Se på video
Dagens video inneholder et bonustips til slutt for å formatere desimaler som en brøkdel.
Videoutskrift
Lær Excel fra Podcast, Episode 2210: Round Up to the Next Quarter Hour.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. I dag, et annet spørsmål fra Atlanta Power Excel-seminaret mitt. Noen gjorde et faktureringsark, og de ønsket å runde opp til neste kvart time, ikke sant? Så for hver oppgave her har de en klient, starttid og sluttid, og for å beregne medgått tid - det er enkelt, hvis det er reelle tider, eller hvis de bare er = E2-D2. Så det sier 55 minutter, men det sier det på en veldig rar måte - "12:55." Vi vil fortsette og formatere dette. Velg tid, og hei, se, det er noen forskjellige valg her: 13:30; 13:30:55, som viser sekunder; og deretter 37:30:55. Nå, for øyeblikket, skal jeg velge 13:30, men vi skal sirkle tilbake senere og undersøke det valget på nytt. Så jeg velger OK og dobbeltklikker for å kopiere den ned,og du ser at det bare gjør matematikken - så her, 25 minutter. Men personen som stilte dette spørsmålet - firmaet de jobber for - de vil runde opp til neste kvart time.
Ok, så kvart time. La oss tenke på tider i Excel. Middag er 0,5. Hvis jeg formaterer det som en tid-- 12:00:00 PM. Og hvis jeg gjør 0,25, vil det være 06:00:00. Greit, så slik dette fungerer er at en hel dag er tallet 1.0, og så hvis vi ville finne ut av 15 minutter, ville det være - det er 24 timer x 4 kvart timer, så vi må sette = 1/96 der inne, og det gir oss 15 minutter. Nå, 1/96 - Jeg kan liksom gjøre den matte i hodet mitt: 24x4; 1/96 - det er absolutt lettere å huske 1/96 enn å huske denne desimalen: 0.01041666667. Greit, så det var lettere å bruke 1/96 der, og så skal vi bruke en funksjon som heter CEILING.
TAK. Se, vi har et valg mellom CEILING og CEILING.MATH for positive tall som er identiske. Det er kortere å skrive CEILING, så jeg skal bruke CEILING. Og avrundet, det er 1/96 av en dag - som er 15 minutter - og det er riktig svar, i feil format. Vi skal kopiere disse formatene over - Ctrl + C, og deretter Alt + E + S + T for å lime inn spesielle formater - og de 55 minuttene runder opp til neste time. Dobbeltklikk for å kopiere det ned, og se, de 35 minuttene runder opp til de neste 45; 25 runder opp til neste 30. Det ser ut til å fungere, selv om personen som stilte dette spørsmålet - de avrunder til neste 15. Kanskje til og med firmaet ditt bruker noe annet.
Greit, så for å avrunde til nærmeste kvart time bruker vi 1/96. Men for å runde til nærmeste 5 minutter - det er 24 timer, det er 12 5-minutters perioder i hver time, så 12x4 = 288. Bruk 1/288 til å runde til nærmeste 5 minutter. Hvis du avrunder til nærmeste 6 minutter - det er 10 perioder på 6 minutter x 24 timer; du avrunder til nærmeste 1/240. For å avrunde til nærmeste minutt, velg til nærmeste 1/1440.
Greit, så du kan gjøre dette, og vi har også et valg. CEILING kommer alltid til å rundes opp, så 16 minutter - la oss gå til 1/96 der - 16 minutter kommer til å runde opp til 30, og selv om jeg er sikker på at advokaten min synes det er en fin måte å fakturere, er det egentlig ikke rettferdig for meg - klienten. OK, så du vet at CEILING vil rundes opp. Advokaten min kommer aldri til å bruke dette: Denne etasjen vil alltid rundes ned. Men det som er det fineste her er sannsynligvis å bruke MROUND. Så, = tidens MROUND til nærmeste 1/96, og det vil rundes ned for alt opp til ca 22,5 minutter; og deretter, etter det, vil den avrundes. Greit, så det runder til hva det er nærmest. Så du må velge om du skal bruke GULV, MROUND eller TAK, og hva divisoren din blir - om det 's kommer til nærmeste 5 minutter, nærmeste minutt, eller, vet du, hva som helst. Ok, så der avrunder vi.
Nå, et bonustips her - dette var Larry i det seminaret - Larry sier: "Hei, se, det er ikke veldig bra å vise fakturerbar tid i slike tider; vi vil virkelig konvertere til timer fordi du virkelig skal å fakturere til en viss pris - du vet, $ 35 / time, $ 100 / time, $ 500 / time, uansett hvilken pris du har - og vi må kunne multiplisere timene x prisene. Så det ville være hyggelig å være i stand til å konvertere disse timene til en desimal tid. Så vi tar den tiden i G2 og multipliserer den med 24, og det vil gi oss et desimalt antall timer, men det kommer til å være i feil format. De er kommer til å velge et tidsformat i dette tilfellet, når det ikke skal være et tidsformat i det hele tatt; det skal bare være i tallformat. Og sørg for å ta med minst to desimaler. Dobbeltklikk for å kopiere det ned, og vi får våre tider. "
Greit, nå, når vi kommer tilbake til spørsmålet vårt om "Hvilket tallformat skal vi bruke?", Er dette et veldig bra sted å snakke om "Hvorfor?". Hvis vi velger alle disse tallene, her, ser vi at summen er 53 timer, og hvis jeg kommer ned hit og legger inn en SUM-funksjon, får vi 53 timer. Det er perfekt. Men hvis jeg bruker SUM-funksjonen under en tidskolonne, og jeg gjør Alt + = eller AUTOSUM, gir det meg 5 timer … 5 timer. Hva er riktig - 53 eller 5?
Vel, det viser seg i en eller annen merkelig forstand, de har begge rett. Men her er hvorfor de begge har rett. Jeg skal gå inn i en funksjon som heter = NÅ. Du ser at jeg faktisk tar opp dette tilbake 9. mai, så for 20 dager siden, og klokka er 07:08. Og med = NÅ, noen ganger vil jeg vise dato og klokkeslett, men andre ganger vil jeg bare for å vise datoen. Så jeg kan lage = NÅ gi meg gjeldende dato, eller, jeg kan lage = NÅ gi meg gjeldende tid. Og så antar nummerfunksjonene gjerne avspenningen eller fjerner datoen. Og denne vi valgte her nede, 13:30, sier: "Hei, jeg vil ikke se antall dager, bare vis meg tiden." Ja, det er problemet - for dette er egentlig 2 dager og 5 timer - 2 dager er 48 timer - 2 dager og 5 timer. Og vi fortalte det - vi sa til Excel, "Hei, ikke gidder å vise meg dagene. "Vel, vi visste ikke at vi fortalte Excel det, men det var det vi fortalte Excel da vi valgte denne eller denne. Greit, så du er tvunget til å kom ned hit til 37:30: 55. Det er den som skal vise oss hele timetallet, og vi får det riktige svaret på 53 timer.
Men det virker veldig dumt å vise de ekstra sekundene når vi avrunder til 15 minutter. Så, la oss gå tilbake til det. Når du har valgt 37:30:55 for å lære tallformatet bak dette, klikker du på Egendefinert. Du vil se at tingen som får det til å fungere er firkantede parenteser rundt timen. Men vi trenger ikke: ss. Så, nå kan vi redigere det tilpassede formatet, og så har vi timer og minutter. Vi trenger ikke å forstå i dag hva; @ betyr. Det sier at for tekst, bare vis teksten; men bare la den delen være der, selv om du ikke forstår den. Klikk på OK, så har vi tiden vår i timer og minutter, og utelater sekundene. Men totalene fungerer. Virkelig, veldig subtil, den 13:30 og den 13:30:55. Du vet, det står ikke "Hei, du ber Excel overse datoen … hele dagene." Men,du er.
Ting som å ta tid matematikk er i denne boka: LIVe: The 54 Greatest Excel Tips of All Time. Klikk på "Jeg" øverst til høyre for å sjekke det ut.
Dette hørtes ut som et så enkelt spørsmål; Jeg kan ikke tro at vi allerede er 7 minutter inne. Men et selskap ønsker å fakturere i kvart timer, og de vil alltid runde opp til neste kvart time. Så, 24 timer om dagen, 24x4 = 96; 15 minutter = 1/96. Så bruk CEILING (A2,1 / 96) for å runde opp til neste høyere kvartal. Bruk MROUND (A2,1 / 96) til å runde til nærmeste kvart time, og bruk deretter FLOOR (A2,1 / 96) for å runde til den nedre kvart timen - går alltid tilbake. Det er 24 timer om dagen, så du kan gjøre 1/288 for å komme til 5-minutters perioder, 1/240 for å komme til 6-minutters perioder, 1/1440 for å runde til nærmeste minutt. Og så, bonustipset der på slutten (hvilket tallformat) - du vil alltid gå tilbake til det 37:30:55 hvis du skal vise timer utover 24 timer.
For å laste ned arbeidsboken fra dagens video, besøk den URL der nede i Youtube-beskrivelsen.
Jeg vil takke deg for at du var innom; vi sees neste gang for en ny netcast fra.
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.
Last ned Excel-fil
For å laste ned Excel-filen: rund-til-kvart-time.xlsx
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Venner lar ikke venner bruke INDIREKTE"
Jordan Goldmeier
