Excel: Konvertere Datoer Til Kvartaler
Excel har innebygde funksjoner for å returnere år, månedsnummer, månedsnavn, ukenummer, ukedag eller dag fra en hvilken som helst celle som inneholder en dato. Men det er ingen innebygd måte å returnere et kvart nummer på. Kvartalene er vanskeligere fordi definisjonen av et kvartal varierer avhengig av sluttdatoen for regnskapsåret.
FOR KALENDERÅR
hvis regnskapsåret ditt slutter 31. desember, kan du bruke en kort formel. (Figur 1 viser Excel-funksjonene du kan bruke til å konvertere en dato til år, måneder, uker eller ukedager.) Bruk først = MÅNED () – funksjonen til å konvertere datoen til et månedsnummer. Del resultatet med 3. Datoer i januar vil returnere 0.33, februar 0.67, Mars 1.00, April 1.33, og så videre. Ved å bruke = ROUNDUP () – funksjonen, vil du ha 1 for januar, februar Og Mars; 2 for April til juni; og så videre.
hvis du vil vise kvartalet som » Q1 «eller» Qtr 1″, setter du sammen den aktuelle teksten før resultatet. I Figur 2 bruker celle A7 en formel på = » Q » & ROUNDUP(MÅNED (A2)/3,0)&»-«&ÅR (A2) for å returnere » Q1-2017.»
For ANDRE REGNSKAPSÅR
Regnskapsår som slutter 31. januar er vanlig i detaljhandelsselskaper. Offentlige entreprenører kan bruke et regnskapsår slutten av September 30. Hvis du vil beregne et regnskapskvartal, konverterer du først datoen til et månedsnummer ved hjelp av =MÅNED(). Dette resultatet er begrenset til 12 heltall fra 1 til 12.
du kan bruke EN FINN. RAD-formel til å konvertere månedsnummer til kvartnummer. Men formelen =FINN. RAD(MÅNED (A2), {1,4;2,1;5,2;8,3;11,4},2) det er vanskelig å huske og skrive. Matrisekonstanten inne i de krøllete bøylene har en oppslagstabell på 5 rader med 2 kolonner.
i Stedet vil = VELG () – funksjonen la deg angi 12 kvartals tall som tilsvarer måneder 1 til 12. Hvis regnskapsåret slutter i januar, vil du bruke = VELG(MÅNED (A2),4,1,1,1,2,2,2,3,3,3,4,4). For et regnskapsår som slutter I Mars, bruk = VELG(MÅNED (A2),4,4,4,1,1,1,2,2,2,3,3,3).
hvis du foretrekker å vise kvartalet Som Q1 i stedet for 1, kan du bruke ampersand til å sette sammen en » Q » før VELG-funksjonen. Resultatene vises I cellene A13 og A20 I Figur 2, med formlene vist I D13 Og D20.
TILFØYER ET REGNSKAPSÅR TIL KVARTALET
i noen selskaper bestemmes regnskapsåret av kalenderåret der den 12. måneden i året oppstår. For eksempel vil et regnskapsår som går fra februar 2017 til januar 2018 ha kvartaler kalt Q1-2018 til Q4-2018. I andre selskaper kan regnskapsåret bli referert til som noe sånt som » 17-18.»
for tilfeller der regnskapsåret er året for sluttmåneden, bruk EN IF () – funksjon for å teste om månedsnummeret er mindre enn startmåneden. For eksempel, med et regnskapsår som slutter i januar, vil regnskapsåret være =ÅR (A2) hvis datoen er før februar og =ÅR (A2) + 1 i februar og senere. For å inkludere regnskapsåret med kvartalet kan du derfor bruke =IF(MÅNED(A2)<2,0,1)+ÅR(A2).
for et år som slutter I Mars, vil du endre <2-testen til å være <4: =IF(MÅNED(A2)<4,0,1)+ÅR(A2). For et år som slutter i September, vil du teste for <10: =IF(MÅNED(A2)<10,0,1)+ÅR(A2).
Celle A21 I Figur 2 viser hvordan man sammenkjeder regnskapskvartalet og året sammen ved hjelp av denne lange formelen: =»Q»&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&»-«&IF(MONTH(A17)<2,0,1)+YEAR(A17). Det ville returnere kvartalet i et format Av » Q4-2017.»
hvis du i stedet trenger det å si 17-18, blir den lange formelen enda lenger. Bare for å få årets del av formelen, vil du bruke: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&»-«&TEXT(A2,»YY»),TEXT(A2,»YY»)&»-«&MOD(YEAR(A2)+1,100)).
RETURNERER EN PERIODE I STEDET for EN MÅNED
jeg har nylig gjennomført et seminar FOR IMA® I Nashville. Fordi Tennessee state government har et regnskapsår som slutter i juni, foretrakk de å rapportere med et periodenummer i stedet for en måned. Juli er periode 1, desember er periode 6, januar er periode 7, og så videre. I dette tilfellet brukte mange av kollegaene ET FINN. RAD-bord til høyre for dataene. Men de kan også bruke =VELG(MÅNED (A2),7,8,9,10,11,12,1,2,3,4,5,6) for å oppnå samme resultat.
DATOER KAN IKKE VÆRE TEKST
advarselen for alle disse formlene er at du lagrer datoene som ekte datoer og ikke som tekst. En rask måte å sjekke: Trykk Ctrl Og Grave Accent key ( ` ) for Å bytte Til Vis Formler modus. Ekte datoer vil konvertere til et serienummer som 42783. Hvis du fortsatt ser en tekstdato, fungerer ikke formlene. Trykk Ctrl + ‘ igjen for å avslutte Vis Formler.
jeg har nylig kjørt over et regneark der lederen ønsket datoer for å vise som 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. Suffiksene etter datoen («st», «nd», «rd» og «th») konverterer et kardinaltall til et ordinaltall. Mens Excel Fill Handle vil tillate deg å lage en kolonne med kardinaltall lett, formlene i denne artikkelen blir langt mer komplekse. En formel på 41 tegn vokste til 2438 tegn for å håndtere tekstdatoene (Se Figur 3).
