Et andet spørgsmål fra mit Atlanta Power Excel-seminar:
Hvordan kan du afrunde fakturerbar tid op til den næste kvart time?
Sig, at du registrerer din fakturerbare tid. Du har en starttid og en sluttid. En formel som =E2-D2beregner den forløbne tid.
Resultatet i F2 er 55 minutter. Men standardformateringen viser 12:55. Vælg hele kolonnen. Tryk på Ctrl + 1 for at formatere celler. Vælg Tidskategori. Hvilke af disse skal du bruge? For øjeblikket skal du vælge 13:30:55, men du vil senere se problemet med dette valg.
Din virksomheds politik er at afrunde alle faktureringer op til den næste kvart time. Gør denne beregning: Der er 24 timer om dagen. Der er 4 kvart timer på en time. Multiplicer 24 * 4 for at få 96. Det betyder, at en kvart time svarer til 1/96 af en dag.
Brug formlen =CEILING(F2,1/96)til at afrunde den forløbne tid i F op til den næste kvart time. I Excels metode til lagring af tid er Noon 0,5. 06:00 er 0,25. 12:15 er 1/96 eller 0,010417. Det er lettere at huske 1/96 end 0,010417.
Resultaterne er vist nedenfor. Læg mærke til, hvordan de 35 minutter runder op til 45 minutter. Dette ser ud til at være den formel, min advokat bruger … haha, det er bare en vittighed Esquire Dewey! Sagsøg mig ikke! Jeg ved, de bruger =CEILING(F2,4)…
Pauser her … denne artikel afrunder til den næste kvart time, fordi det er den metode, Larry i mit seminar bruger. Der er en god chance for, at din virksomhed runder til noget andet. Billedet nedenfor viser, hvordan man afrunder til fem minutter eller 1 minut. Du kan bruge lignende logik til at afrunde til de nærmeste 6 minutter (1/240) eller 12 minutter (1/120).
Også - et mildt forslag, der viser forskellen mellem GULV, MROUND og loft. FLOOR-funktionen i B9: B22 runder altid ned. MROUND i C9: C22 afrunder til nærmeste. Loftet i D9: D22 runder op.
Bemærk
Excel-værktøjstip foreslår, at du bruger FLOOR.MATH eller CEILING.MATH i stedet for FLOOR eller CEILING. De nye ".Math" -versioner af disse funktioner håndterer negative tal forskelligt. Da tid i Excel aldrig kan være negativ, er det fint at bruge den almindelige GULV eller loft.
Skift brøkdelen fra 1/96 til 1/288 til runde til fem minutters intervaller.
Konvertering af tider til timer
Et andet forslag fra Larry: Konverter tidspunkterne til ægte timer. På den måde, når du ganger med faktureringssatsen pr. Time, fungerer matematikken. Tag den afrundede forløbne tid og gang med 24 for at få et decimalt antal timer. Sørg for at formatere resultatet som et tal med to decimaler og ikke et tidspunkt.
Hvilket tidsformat skal du bruge?
Det er tid til at tale om disse tidsformateringsvalg: Du kunne have valgt 13:30, 13:30:55 eller 37:30:55. I skærmbillederne ovenfor har jeg brugt 13:30:55. Problemet med dette vises i slutningen af ugen. Når du sammenlægger decimaltimerne i kolonne H, har du 53 fakturerbare timer. Når du summerer gange i G, får du fem timer. Hvad sker der?
Begge er korrekte, hvis du forstår det nummerformat, du valgte. Sig, at du går ind =NOW()i en celle. Du kan formatere cellen til at vise dato og tid, bare dato eller bare tid. Excel har ikke noget problem med at afslutte den 29. maj af 29. maj kl. 08:15:34, hvis du beder den om at gøre det. Svaret i celle G57 er "2 dage og fem timer". Men da du valgte 13:37:55 som tidsformat, bad du Excel om at lade antallet af dage afskaffe og kun vise dig timer, minutter og sekunder.
Hvis du konverterer kolonne G til at bruge formatet 37:30:55, får du det korrekte antal timer:
Billedteksten over det billede fanger det lille problem med formatet 37:30:55: det viser sekunder, hvilket synes unødvendigt, da vi afrunder til de nærmeste femten minutter. Når du har valgt 37:30:55, skal du klikke på kategorien Brugerdefineret. Du kan se, at nummerformateringskoden (h):mm:ss;@bruges. De firkantede parenteser omkring H er den vigtige del.
For at vise dine tidspunkter i timer og minutter skal du redigere nummerformatet for at fjerne: ss.
Se video
Dagens video indeholder et bonustip i slutningen til formatering af decimaler som en brøkdel.
Videoudskrift
Lær Excel fra Podcast, afsnit 2210: Afslut til næste kvartal.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. I dag endnu et spørgsmål fra mit Atlanta Power Excel-seminar. Nogen lavede et faktureringsark, og de ville afrunde til næste kvart time, ikke? Så for hver opgave her har de en klient, starttid og sluttid og til at beregne den forløbne tid - det er simpelt, hvis det er rigtige tider, eller hvis de bare er = E2-D2. Så det siger 55 minutter, men det siger det på en virkelig underlig måde - "12:55." Vi ønsker at gå videre og formatere dette. Vælg tid, og hej, se, der er et par forskellige valg her: 13:30; 13:30:55, som viser sekunderne; og derefter 37:30:55. Nu, lige nu, skal jeg vælge kl. 13:30, men vi vil cirkel tilbage senere og genoverveje dette valg. Så jeg vælger OK og dobbeltklikker for at kopiere det ned,og du kan se, at det bare laver matematik - så her, 25 minutter. Men den person, der stillede dette spørgsmål - det firma, de arbejder for - de vil afrunde til næste kvart time.
Okay, så kvart time. Lad os tænke på tider i Excel. Middag er 0,5. Hvis jeg formaterer det som en tid - 12:00:00 PM. Og hvis jeg gør 0,25, bliver det 06:00:00. Okay, så den måde dette fungerer på er, at en hel dag er tallet 1.0, og så hvis vi ville finde ud af 15 minutter, ville det være - der er 24 timer x 4 kvart timer, så vi bliver nødt til at sætte 1/96 derinde, og det giver os 15 minutter. Nu, 1/96 - Jeg kan slags lave den matematik i mit hoved: 24x4; 1/96 - det er bestemt lettere at huske 1/96 end at huske denne decimal: 0.01041666667. Okay, så det var lettere at bruge 1/96 der, og så skal vi bruge en funktion kaldet CEILING.
LOFT. Se, vi har et valg mellem CEILING og CEILING.MATH for positive tal, der er identiske. Det er kortere at skrive CEILING, så jeg vil bruge CEILING. Og afrundet er der 1/96 af en dag - hvilket er 15 minutter - og det er det rigtige svar i det forkerte format. Vi skal kopiere disse formater over - Ctrl + C og derefter Alt + E + S + T for at indsætte specielle formater - og de 55 minutter runder op til den næste time. Dobbeltklik for at kopiere det ned, og se de 35 minutter runder op til de næste 45; 25 runder op til de næste 30. Det ser ud til at fungere, skønt den person, der stillede dette spørgsmål - de runder op til de næste 15. Måske bruger din virksomhed måske noget andet.
Okay, så for at afrunde til nærmeste kvart time bruger vi 1/96. Men for at afrunde de nærmeste 5 minutter - der er 24 timer, er der 12 5-minutters perioder i hver time, så 12x4 = 288. Brug 1/288 til at afrunde til nærmeste 5 minutter. Hvis du afrunder til de nærmeste 6 minutter - er der 10 perioder på 6 minutter x 24 timer; du runde til nærmeste 1/240. Vælg til nærmeste 1/1440 for at afrunde til nærmeste minut.
Okay, så du kan gøre dette, og vi har også et valg. Lofter afrunder altid, så 16 minutter - lad os gå til 1/96 der - 16 minutter rundes op til 30, og selvom min advokat er sikker på, at det er en fantastisk måde at fakturere, er det ikke rigtig fair for mig - klienten. Okay, så du ved, at CEILING afrunder. Min advokat vil aldrig bruge dette: Denne etage vil altid rundes ned. Men sandsynligvis er det, der er den smukkeste her, at bruge MROUND. Så = TIDSMROUND til nærmeste 1/96, og det afrundes for alt op til ca. 22,5 minutter; og derefter derefter rundes det op. Okay, så det afrunder til hvad det er tættest på. Så du bliver nødt til at vælge, om du vil bruge GULV, MROUND eller loft, og hvad din skillevæg skal være - om det 's vil være de nærmeste 5 minutter, det nærmeste minut eller, ved du hvad som helst. Okay, så der afrunder vi.
Nu, et bonustip her - dette var Larry i det seminar - Larry siger, "Hej, se, det er ikke rigtig godt at vise fakturerbar tid i sådanne tider; vi vil virkelig konvertere til timer, fordi du virkelig går at fakturere med en eller anden hastighed - du ved, $ 35 / time, $ 100 / time, $ 500 / time, uanset hvad din sats er - og vi skal være i stand til at multiplicere timerne x satserne. Så det ville være rart at være i stand til at konvertere disse timer til en decimal tid. Så vi tager den tid i G2 og ganger den med 24, og det vil give os et decimalt antal timer, men det vil være i det forkerte format. De er vil vælge et tidsformat i dette tilfælde, når det overhovedet ikke skal være et tidsformat; det skal bare være i nummerformat. Og sørg for at medtage mindst to decimaler. Dobbeltklik for at kopiere det ned, og vi får vores tider. "
Okay, nu vender vi tilbage til vores spørgsmål her om "Hvilket nummerformat skal vi bruge?", Dette er et rigtig godt sted at tale om "Hvorfor?". Hvis vi vælger alle disse tal her, ser vi, at summen er 53 timer, og hvis jeg kommer herned og sætter en SUM-funktion, får vi 53 timer. Det er perfekt. Men hvis jeg bruger SUM-funktionen under en tidssøjle, og jeg gør Alt + = eller AUTOSUM, giver det mig 5 timer … 5 timer. Hvilket er rigtigt - 53 eller 5?
Nå, det viser sig i en eller anden underlig forstand, de har begge ret. Men her er hvorfor de begge har ret. Jeg går ind i en funktion kaldet = NU. Du kan se, at jeg faktisk optager dette tilbage den 9. maj, så for 20 dage siden, og klokken er 07:08 og med = NU, nogle gange vil jeg vise dato og klokkeslæt, men andre gange vil jeg bare for at vise datoen. Så jeg kan lave = NU give mig den aktuelle dato, eller jeg kan lave = NU give mig det aktuelle tidspunkt. Og så er Number-funktionerne glade for enten at afkorte fristen eller fjerne datoen fra. Og denne, som vi valgte hernede, 13:30, siger: "Hej, jeg vil ikke se antallet af dage; bare vis mig tiden." Ja, det er problemet - for dette er virkelig 2 dage og 5 timer - 2 dage er 48 timer - 2 dage og 5 timer. Og vi fortalte det - vi sagde til Excel, "Hej, gider ikke at vise mig dagene. "Nå, vi vidste ikke, at vi fortalte Excel det, men det var det, vi fortalte Excel, da vi valgte denne eller denne. Okay, så du er tvunget til at kom ned her til 37:30: 55. Det er den, der viser os det fulde antal timer, og vi får det rigtige svar på 53 timer.
Men det virker virkelig fjollet at vise de ekstra sekunder, når vi afrunder til 15 minutter. Så lad os gå tilbage på det. Når du har valgt 37:30:55 for at lære nummerformatet bag dette, skal du klikke på Brugerdefineret. Du vil se, at det, der får det til at fungere, er de firkantede parenteser omkring timen. Men vi har ikke brug for: ss. Så nu kan vi redigere det brugerdefinerede format, og så har vi timer og minutter. Vi behøver ikke at forstå i dag, hvad; @ betyder. Det siger, at for tekst skal du bare vise teksten; men lad bare den del være der, selvom du ikke forstår det. Klik på OK, og vi har nu vores tid i timer og minutter og udelader sekunder. Men totalerne fungerer. Virkelig, virkelig subtil, den 13:30 og den 13:30:55. Du ved, det siger ikke "Hej, du beder Excel om at ignorere datoen … de fulde dage." Men,du er.
Ting som at tage tidsmatematik findes i denne bog: LIVe: The 54 Greatest Excel Tips of All Time. Klik på det "I" i øverste højre hjørne for at tjekke det ud.
Dette lød som et så let spørgsmål; Jeg kan ikke tro, at vi allerede er 7 minutter inde. Men et firma ønsker at fakturere i kvart timer, og de vil altid afrunde til næste kvart time. Så 24 timer om dagen, 24x4 = 96; 15 minutter = 1/96. Så brug CEILING (A2,1 / 96) til at runde op til det næste højere kvartal. Brug MROUND (A2,1 / 96) til at afrunde til nærmeste kvart time, og brug derefter GULV (A2,1 / 96) til at runde til den nedre kvart time - går altid tilbage. Der er 24 timer om dagen, så du kan gøre 1/288 for at komme til 5-minutters perioder, 1/240 for at komme til 6-minutters perioder, 1/1440 for at afrunde til nærmeste minut. Og så bonustipet der i slutningen (hvilket nummerformat) - du vil altid vende tilbage til det 37:30:55, hvis du vil vise timer ud over 24 timer.
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.
Download Excel-fil
For at downloade 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 at tænke over:
"Venner tillader ikke venner at bruge INDIREKTE"
Jordan Goldmeier
