Excel: datums converteren naar kwartalen
Excel heeft ingebouwde functies voor het retourneren van jaar, maandnummer, maandnaam, weeknummer, weekdag of dag uit een cel met een datum. Maar er is geen ingebouwde manier om een kwart nummer terug te geven. Kwartalen zijn moeilijker omdat de definitie van een kwart varieert afhankelijk van de einddatum van het boekjaar.
voor kalenderjaren
als uw boekjaar eindigt op 31 December, kunt u een korte formule gebruiken. (Figuur 1 toont de Excel-functies die u kunt gebruiken om een datum om te zetten in jaren, maanden, weken of weekdagen.) Gebruik eerst de = MONTH() functie om de datum naar een maandnummer te converteren. Deel dat resultaat door 3. Data in januari zal terugkeren 0,33, februari 0,67, maart 1,00, April 1,33, en ga zo maar door. Door de functie =ROUNDUP() te gebruiken, heb je 1 Voor januari, februari en maart; 2 voor April tot en met juni; enzovoort.
om het kwartaal als “Q1” of “Qtr 1″ te tonen, voegt u de juiste tekst voor het resultaat samen. In Figuur 2 gebruikt cel A7 een formule van = ” Q ” &ROUNDUP (MONTH (A2)/3,0)&”-“&jaar (A2) om terug te keren “Q1-2017.”
voor andere boekjaren
zijn boekjaren eindigend op 31 januari gebruikelijk in retailbedrijven. Overheidsaannemers kunnen gebruik maken van een fiscaal jaar eind September 30. Om een fiscaal kwartaal te berekenen, converteert u eerst de datum naar een maandnummer met behulp van =MONTH(). Dit resultaat is beperkt tot 12 gehele getallen van 1 tot 12.
u kunt een VLOOKUP-formule gebruiken om het maandnummer naar het kwartaalnummer te converteren. Maar de formule = VLOOKUP (MONTH (A2), {1,4;2,1;5,2;8,3;11,4},2) is moeilijk te onthouden en te typen. De array constante in de krullende accolades bevat een 5-rij door 2-kolom lookup tabel.
in plaats daarvan laat de functie = CHOOSE () u 12 kwartnummers opgeven die overeenkomen met maanden 1 tot en met 12. Als je fiscale jaar eindigt in januari, zou je gebruiken = kiezen (maand (A2),4,1,1,1,2,2,2,3,3,3,4,4). Voor een fiscaal jaar eindigend in Maart, use = CHOOSE (MONTH (A2),4,4,4,1,1,1,2,2,2,3,3,3).
als u het kwartaal liever als Q1 in plaats van 1 wilt weergeven, kunt u de ampersand gebruiken om een “Q” samen te voegen voor de CHOOSE-functie. De resultaten worden weergegeven in de cellen A13 en A20 van Figuur 2, met de formules in D13 en D20.
als een boekjaar wordt toegevoegd aan het kwartaal
in sommige ondernemingen wordt het boekjaar bepaald door het kalenderjaar waarin de 12e maand van het jaar plaatsvindt. Een fiscaal jaar dat loopt van februari 2017 tot januari 2018 zou bijvoorbeeld kwartalen hebben genaamd Q1-2018 tot en met Q4-2018. In andere bedrijven, het fiscale jaar zou kunnen worden aangeduid als iets als ” 17-18.”
gebruik een IF () – functie om te testen of het maandnummer minder is dan de beginmaand. Bijvoorbeeld, met een fiscaal jaar dat eindigt in januari, zal het fiscale jaar =jaar (A2) zijn als de datum vóór februari is en =jaar(A2)+1 in februari en later. Dus, om het fiscale jaar met het kwartaal op te nemen, zou je kunnen gebruiken =IF(MONTH(A2)<2,0,1)+YEAR(A2).
voor een jaar dat eindigt in Maart, zou u de <2-test veranderen in < 4: = IF(MONTH(A2)<4,0,1)+YEAR (A2). Voor een jaar dat eindigt in September, test je voor <10: = IF(MONTH(A2)<10,0,1)+YEAR (A2).
cel A21 in Figuur 2 laat zien hoe het fiscale kwartaal en het fiscale jaar met elkaar kunnen worden verbonden met behulp van deze lange formule: =”Q”&CHOOSE(MONTH(A17),4,1,1,1,2,2,2,3,3,3,4,4)&”-“&IF(MONTH(A17)<2,0,1)+YEAR(A17). Dat zou het kwartaal terug te keren in een formaat van “Q4-2017.”
Als u in plaats daarvan 17-18 wilt zeggen, wordt de lange formule nog langer. Gewoon om het jaargedeelte van de formule te krijgen, zou je gebruiken: =IF(MONTH(A2)<4,MOD(YEAR(A2)-1,100)&”-“&TEXT(A2,”YY”),TEXT(A2,”YY”)&”-“&MOD(YEAR(A2)+1,100)).
terugkomend op een periode in plaats van een maand
heb ik onlangs een seminar gehouden voor IMA® in Nashville. Omdat de Tennessee state government een fiscaal jaar heeft dat eindigt in juni, gaven ze er de voorkeur aan om een periode nummer te gebruiken in plaats van een maand. Juli is Periode 1, December Is periode 6, Januari Is periode 7, enzovoort. In dit geval gebruikten veel collega ‘ s een VLOOKUP-tabel aan de rechterkant van de gegevens. Maar ze konden ook gebruiken = kiezen (maand (A2),7,8,9,10,11,12,1,2,3,4,5,6) om hetzelfde resultaat te bereiken.
datums kunnen geen tekst
het voorbehoud voor al deze formules is dat u uw datums als echte datums opslaat en niet als tekst. Een snelle manier om te controleren: Druk op Ctrl en de Grave Accent toets ( ‘ ) om te schakelen naar formules weergeven modus. Echte datums converteren naar een serienummer zoals 42783. Als u nog steeds een tekstdatum ziet, werken de formules niet. Druk nogmaals op Ctrl+ ‘ om formules weergeven te sluiten.
ik kwam onlangs een werkblad tegen waarin de manager datums wilde weergeven als 1st Feb 2017, 2nd Feb 2017, 3rd Feb 2017, 4th Feb 2017. De achtervoegsels na de datum (“st, “” nd, “” rd, “en” th”) zetten een kardinaalgetal om in een ordinaalgetal. Terwijl u met de Excel-vulgreep eenvoudig een kolom met hoofdtelwoorden kunt maken, worden de formules in dit artikel veel complexer. Een formule van 41 tekens groeide uit tot 2.438 tekens om de tekstdatums te verwerken (zie Figuur 3).
