Excel: Konvertera datum till kvartal
Excel har inbyggda funktioner för att returnera år, månadsnummer, månadsnamn, veckonummer, veckodag eller dag från en cell som innehåller ett datum. Men det finns inget inbyggt sätt att returnera ett kvartalsnummer. Kvartal är svårare eftersom definitionen av kvartal varierar beroende på räkenskapsårets slutdatum.
för kalenderår
om ditt räkenskapsår slutar den 31 December kan du använda en kort formel. (Figur 1 visar de Excel-funktioner som du kan använda för att konvertera ett datum till år, månader, veckor eller vardagar.) Använd först funktionen =månad () för att konvertera datumet till ett månadsnummer. Dela resultatet med 3. Datum i januari kommer att återvända 0.33, februari 0.67, Mars 1.00, April 1.33, och så vidare. Genom att använda funktionen = ROUNDUP () har du 1 för januari, februari och mars; 2 för April till juni; och så vidare.
för att visa kvartalet som ”Q1” eller ”Qtr 1″, sammanfoga lämplig text före resultatet. I Figur 2 använder cell A7 en formel av = ” Q ” & ROUNDUP(månad (A2)/3,0)&”-”&år (A2) för att återvända ”Q1-2017.”
för andra räkenskapsår
räkenskapsår som slutar 31 januari är vanliga i detaljhandelsföretag. Statliga entreprenörer kan använda ett räkenskapsår i slutet av September 30. För att beräkna ett skattekvartal, konvertera först datumet till ett månadsnummer med =månad(). Detta resultat är begränsat till 12 heltal från 1 till 12.
du kan använda en VLOOKUP-formel för att konvertera månadsnummer till kvartalsnummer. Men formeln =LETARAD(månad (A2), {1,4;2,1;5,2;8,3;11,4},2) är svårt att komma ihåg och skriva. Arraykonstanten inuti de lockiga hängslen har en 5-rad med 2-kolumns uppslagstabell.
istället skulle funktionen =välj () låta dig ange 12 kvartalsnummer som motsvarar månaderna 1 till 12. Om ditt räkenskapsår slutar i januari skulle du använda =Välj(månad (A2),4,1,1,1,2,2,2,3,3,3,4,4). För ett räkenskapsår som slutar i Mars, använd =Välj(månad (A2),4,4,4,1,1,1,2,2,2,3,3,3).
om du föredrar att visa kvartalet som Q1 istället för 1 kan du använda ampersand för att sammanfoga en ”Q” före funktionen välj. Resultaten visas i cellerna A13 och A20 i Figur 2, med formlerna som visas i D13 och D20.
lägga till ett räkenskapsår till kvartalet
i vissa företag bestäms räkenskapsåret av det kalenderår då årets 12: e månad inträffar. Till exempel skulle ett räkenskapsår som löper från februari 2017 till januari 2018 ha kvartaler som heter Q1-2018 till Q4-2018. I andra företag kan räkenskapsåret kallas något som ” 17-18.”
för det fall där räkenskapsåret är året för den avslutande månaden, använd en IF () – funktion för att testa om månadens nummer är mindre än startmånaden. Till exempel, med ett räkenskapsår som slutar i januari, kommer räkenskapsåret att vara =år(A2) om datumet är före februari och =år(A2)+1 i februari och senare. Således, för att inkludera räkenskapsåret med kvartalet, kan du använda =IF(månad(A2)<2,0,1)+år (A2).
för ett år som slutar i mars skulle du ändra <2-testet för att vara <4: =IF(månad(A2)<4,0,1)+år(A2). För ett år som slutar i September skulle du testa för <10: =IF(månad(A2)<10,0,1)+år(A2).
Cell A21 i Figur 2 visar hur man sammanfogar skattekvartalet och året tillsammans med denna långa 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 skulle återvända kvartalet i ett format av ” Q4-2017.”
om du istället behöver det för att säga 17-18 blir den långa formeln ännu längre. Bara för att få årdelen av formeln, du skulle använda: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-”&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-”&MOD(YEAR(A2)+1,100)).
åter en PERIOD i stället för en månad
jag har nyligen genomfört ett seminarium för ima Gigabit i Nashville. Eftersom Tennessee state government har ett räkenskapsår som slutar i juni föredrog de att rapportera med ett periodnummer istället för en månad. Juli är period 1, December är period 6, januari är period 7 och så vidare. I detta fall, en hel del av de medarbetare som används en LETARAD tabell bort till höger om data. Men de kan också använda =Välj(månad (A2),7,8,9,10,11,12,1,2,3,4,5,6) för att uppnå samma resultat.
datum kan inte vara TEXT
förbehållet för alla dessa formler är att du lagrar dina datum som riktiga datum och inte som text. Ett snabbt sätt att kontrollera: tryck på Ctrl och Grave Accent-tangenten ( ’ ) för att växla till Show Formulas-läge. Riktiga datum kommer att konvertera till ett serienummer som 42783. Om du fortfarande ser ett textdatum fungerar inte formlerna. Tryck Ctrl` ’ igen för att avsluta visa formler.
jag sprang nyligen över ett kalkylblad där chefen ville att datum skulle visas som 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. Suffixerna efter datumet (”st”, ”nd”, ”rd” och ”th”) konverterar ett kardinaltal till ett ordinärt tal. Medan Excel-fyllningshandtaget gör att du enkelt kan skapa en kolumn med kardinalnummer blir formlerna i den här artikeln mycket mer komplexa. En formel med 41 tecken växte till 2 438 tecken för att hantera textdatum (se Figur 3).
