Konvertering af datoer til kvartaler
har indbyggede funktioner til returnering af år, månedsnummer, månedsnavn, ugenummer, ugedag eller dag fra enhver celle, der indeholder en dato. Men der er ingen indbygget måde at returnere et kvart nummer på. Kvartaler er vanskeligere, fordi definitionen af et kvartal varierer afhængigt af regnskabsårets slutdato.
for kalenderår
hvis dit regnskabsår slutter den 31.December, kan du bruge en kort formel. (Figur 1 viser de funktioner, du kan bruge til at konvertere en dato til år, måneder, uger eller hverdage.) Brug først funktionen = måned () til at konvertere datoen til et månedsnummer. Del resultatet med 3. Datoer i Januar vender tilbage 0.33, Februar 0.67, Marts 1.00, April 1.33 og så videre. Ved at bruge funktionen =ROUNDUP () har du 1 For januar, februar og marts; 2 for April til juni; og så videre.
hvis du vil vise kvartalet som “1. kvartal” eller “1.kvartal”, skal du sammenkæde den relevante tekst før resultatet. I figur 2 bruger celle A7 en formel på =”K” & ROUNDUP(måned (A2)/3,0)&”-“&år (A2) for at vende tilbage “1.kvartal-2017.”
for andre regnskabsår
regnskabsår, der slutter 31.Januar, er almindelige i detailvirksomheder. Offentlige entreprenører kan bruge et regnskabsår slutningen af September 30. For at beregne et finanspolitisk kvartal skal du først konvertere datoen til et månedsnummer ved hjælp af =måned(). Dette resultat er begrænset til 12 heltal fra 1 til 12.
du kan bruge en VLOOKUP-formel til at konvertere månedsnummer til kvartalsnummer. Men formlen =VLOOKUP (måned (A2), {1,4;2,1;5,2;8,3;11,4},2) Det er svært at huske og skrive. Array-konstanten inde i de krøllede seler har en 5-række med 2-kolonne opslagstabel.
i stedet vil funktionen =Vælg () lade dig angive 12 kvartalstal, der svarer til måneder 1 til 12. Hvis dit regnskabsår slutter i januar, vil du bruge =Vælg(måned (A2),4,1,1,1,2,2,2,3,3,3,4,4). For et regnskabsår, der slutter i Marts, brug =vælg(måned (A2),4,4,4,1,1,1,2,2,2,3,3,3).
hvis du foretrækker at vise kvartalet som kvartal 1 i stedet for 1, kan du bruge ampersand til at sammenkæde et “spørgsmål” før funktionen Vælg. Resultaterne er vist i cellerne A13 og A20 i figur 2, med formlerne vist i D13 og D20.
tilføjelse af et regnskabsår til kvartalet
i nogle virksomheder bestemmes regnskabsåret af det kalenderår, hvor årets 12.måned finder sted. For eksempel ville et regnskabsår, der løber fra februar 2017 til januar 2018, have kvartaler kaldet 1.kvartal-2018 til 4. kvartal-2018. I andre virksomheder kan regnskabsåret betegnes som noget i retning af ” 17-18.”
i det tilfælde, hvor regnskabsåret er året for slutmåneden, skal du bruge en IF () – funktion til at teste, om månedens nummer er mindre end startmåneden. For eksempel med et regnskabsår, der slutter i januar, vil regnskabsåret være =år(A2), hvis datoen er før februar og =år(A2)+1 i februar og senere. For at inkludere regnskabsåret med kvartalet kan du således bruge =IF(måned(A2)<2,0,1)+år (A2).
i et år, der slutter i marts, vil du ændre <2 testen til at være <4: =if(måned(A2)< 4,0,1)+år(A2). I et år, der slutter i September, ville du teste for < 10: =if(måned(A2)<10,0,1)+år(A2).
celle A21 i figur 2 viser, hvordan man sammenkæder skattekvartalet og året sammen ved hjælp af denne lange formel: =”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 af “4.kvartal 2017.”
hvis du i stedet har brug for det for at sige 17-18, bliver den lange formel endnu længere. Bare for at få årets del af formlen, ville du bruge: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).
returnering af en periode i stedet for en måned
jeg har for nylig gennemført et seminar for IMA Larra i Nashville. Fordi Tennessee-statsregeringen har et regnskabsår, der slutter i Juni, de foretrak at rapportere ved hjælp af et periodenummer i stedet for en måned. Juli er Periode 1, December er periode 6, Januar er periode 7, og så videre. I dette tilfælde brugte mange af kollegerne en VLOOKUP-tabel til højre for dataene. Men de kunne også bruge =Vælg(måned (A2),7,8,9,10,11,12,1,2,3,4,5,6) for at opnå det samme resultat.
datoer kan ikke være tekst
advarslen for alle disse formler er, at du gemmer dine datoer som rigtige datoer og ikke som tekst. En hurtig måde at kontrollere: Tryk på Ctrl og Grave Accent-tasten (`) for at skifte til Vis formler-tilstand. Rigtige datoer konverteres til et serienummer som 42783. Hvis du stadig ser en tekstdato, fungerer formlerne ikke. Tryk på Ctrl` ‘ igen for at afslutte Vis formler.
jeg løb for nylig over et regneark, hvor manageren ønskede, at datoer skulle vises som 1.februar 2017, 2. februar 2017, 3. februar 2017, 4. februar 2017. Suffikserne efter datoen (“st”, “nd”, “rd” og “th”) Konverterer et kardinaltal til et ordinært tal. Mens udfyldningshåndtaget giver dig mulighed for nemt at oprette en kolonne med kardinaltal, bliver formlerne i denne artikel langt mere komplekse. En formel på 41 tegn voksede til 2.438 tegn for at håndtere tekstdatoer (se figur 3).
