Hoe krijg je een kwart date?
In Excel is er geen directe functie om een kwartaal uit een datum te halen. Maar je kunt hiervoor een formule maken.
Ja, u kunt een formule gebruiken om een kwartaal vanaf een datum te berekenen. Er is zelfs meer dan één methode die u kunt gebruiken. Oké, zoals je weet zitten er in totaal vier kwartalen in een jaar, maar feit is dat je op verschillende manieren een kwartje uit een date kunt halen.
De eerste is, uitgaande van een jaar op normale kalenderbasis, of de tweede op belastingkalenderbasis. En in het bericht van vandaag zal ik je methoden laten zien om het kwartaalnummer te krijgen met behulp van beide kalenders, en ik zal hiervoor ook meer dan één methode met je delen.
Dus laten we beginnen.
1. Ontvang een kwartaal met de functies ROUNDUP en MONTH
Het gebruik van een combinatie van ROUNDUP en MONTH is de beste manier om het kwartaal van een datum te vinden. Het retourneert een kwart als een getal (zoals 1,2,3,4).
Hier is de formule.
=ROUNDUP(MONTH(A1)/3,0)
Hier gebruiken we 26 mei 2018 als datum en de formule retourneert 2 in het resultaat. U kunt de onderstaande formule gebruiken als u een Q vóór het kwartaalnummer wilt toevoegen.
="Q-"&ROUNDUP(MONTH(A1)/3,0)
Hoe werkt deze formule?
Deze formule werkt in drie delen.
In het eerste deel gebruikten we de functie MAAND en verwezen we naar de datum die het maandnummer retourneert.
Daarna hebben we in het tweede deel dit maandnummer door 3 gedeeld, wat ons een getal met decimalen oplevert.
Ten slotte hebben we in het derde deel de functie ROUNDUP gebruikt om dit getal af te ronden naar decimalen, wat ons het werkelijke kwartaal oplevert.
Hier is het echte werk: je weet dat er vier kwartalen en twaalf maanden zijn. RECHTS? En als je alle maandnummers door drie deelt (ja, alleen met 3), krijg je zoiets als hieronder.
En als u ze naar boven afrondt, krijgt u altijd het kwartaalnummer van de maand.
2. Ontvang een kwartaal met behulp van de fiscale kalender
In het bovenstaande voorbeeld hebben we een eenvoudige formule gebruikt om een kwartaal vanaf een datum te berekenen, maar soms moet u een fiscale kalender gebruiken.
Wat is een boekjaar?
Een boekjaar (FY) is een periode van twaalf maanden die een bedrijf of overheid gebruikt voor boekhoudkundige doeleinden en om financiële overzichten op te stellen. Een boekjaar hoeft niet overeen te komen met een kalenderjaar…
En voor een berekening van een fiscaal kalenderkwartaal moeten we een andere formule gebruiken en in deze formule moeten we de functies SELECT en MAAND combineren.
Hier is de formule:
=CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
(Hier heb ik het Indiase fiscale jaar gebruikt dat begint op 1 april en eindigt op 31 maart)
Het retourneert 1 in het resultaat omdat de maand mei is en ons jaar begint vanaf april en mei de tweede maand van het jaar is die onder het eerste kwartaal valt.
Hoe werkt deze formule?
Hier hebben we twee verschillende functies gecombineerd en het werkt in twee delen.
In het eerste deel gebruikten we de functie MAAND om het maandnummer uit de datum te halen en hier retourneert het 5.
Daarna kozen we in het tweede deel de functie om een getal te krijgen op basis van het maandnummer dat werd geretourneerd door de MAAND-functie.
Meer informatie over CHOOSE vindt u hier.
Als je kijkt, hebben we in de functie KIEZEN 12 verschillende getallen opgegeven (4,4,4,1,1,1,2,2,2,3,3,3), dus als MAAND een getal retourneert, retourneert KIEZEN een getal van deze lijst.
Dit is de deal:
Voor de eerste drie cijfers hebben we 4, wat betekent dat als het 1, 2 of 3 retourneert (januari-maart), we 4 krijgen en de komende drie maanden 1 (dit komt omdat onze jaarbelasting begint vanaf april).
3. Converteer een datum naar kwartaal met behulp van VBA-code
Hier gebruiken we VBA-code om eenvoudig een datum naar het kwartaalnummer te converteren. Hier is de code:
Sub convert2Quarter() Dim Rng As Range For Each Rng In Selection If IsDate(Rng.Value) = True Then Rng.Value = DatePart("q", Rng.Value) Rng.NumberFormat = “General” End If Next Rng End SubOm deze code te gebruiken, voegt u deze eenvoudig toe aan uw VB-editor.
En selecteer vervolgens gewoon de cellen waarin u een datum naar een kwartaalnummer moet converteren en voer ten slotte deze code uit.
Zo werkt deze code…
Wanneer u deze code uitvoert, wordt eerst gecontroleerd of de cel een datum heeft of niet, en als het een datum is, wordt deze geconverteerd naar een kwartaalgetal.
We hebben hier twee verschillende VBA-functies gebruikt:
- ISDATE: Om te controleren of de cel een datum heeft.
- DATEPART: om een datum om te zetten in een kwartaal.
Controleer dit: Top 100 nuttige macrocodes voor VBA-nieuwkomers | Wat is VBA in Excel
4. Haal de startdatum van het kwartaal op vanaf een datum
Laten we het kwartaalnummer vergeten en de startdatum van het kwartaal gebruiken.
Dit betekent dat als we de datum 26 mei 2018 hebben, in plaats van het kwartaalnummer 2, we 1 april 2018 kunnen hebben, wat de startdatum is van de eerste maand van het kwartaal. Hier is de formule die we moeten gebruiken:
=DATE(YEAR(A1),FLOOR.MATH(MONTH (A1)-1,3)+1,1)
Wanneer u deze formule invoert, retourneert deze eenvoudigweg de startdatum van het kwartaal.
Hoe werkt deze formule?
Deze formule bestaat uit vier verschillende delen die u de startdatum van een semester geven.
In het eerste deel gebruikten we de functie MAAND die het maandnummer van de datum retourneert.
Dus als MONTH 5 retourneert als het maandnummer (en vervolgens minus één daarvan), rondt FLOOR dit in het tweede deel af naar 3 en telt er vervolgens één bij op, zodat we 4 krijgen, wat de eerste maand (april) van hetzelfde kwartaal is?
Daarna gebruikten we in het derde deel de JAAR-functie om het jaar uit de werkelijke datum te halen.
En in het vierde deel combineerden we eenvoudigweg de DATE-functie om een datum te creëren die waarden uit de eerste twee delen combineerde.
5. Haal de kwartaaleinddatum op van een datum
Oké, je hebt dus de einddatum van een kwartaal nodig in plaats van de startdatum of het startnummer en daarvoor kunnen we de formule gebruiken:
=DATE(YEAR(A2),((INT((MONTH(A2)-1)/3)+1)*3)+1,1)-1
Voor 26 mei 18 komt het terug op 30 juni 18, de laatste datum van het kwartaal.
Hoe werkt deze formule?
In deze formule hebben we vier verschillende delen.
In het eerste deel combineerden we de functies INT en MONTH. MONTH retourneert het maandnummer van de datum en deelt dit vervolgens door 3. Daarna rondt INT het af op het dichtstbijzijnde gehele getal.
Uiteindelijk vermenigvuldigen we het met 3 om 6 (juni) te krijgen, wat de eindmaand van het kwartaal is, en vervolgens voegen we er 1 aan toe.
In eenvoudige bewoordingen converteert dit deel van de formule eenvoudigweg de maand van de werkelijke datum naar de eerste maand van het volgende kwartaal.
In het tweede deel gebruikten we de JAAR-functie om het jaar uit de werkelijke datum te halen.
Daarna gebruikt u in het derde deel gewoon 1 om de datum aan te vullen met de DATUM-functie.
Sorry! Nog een ding.
In de vierde , minus 1 van de hele formule, kunnen we dus de laatste datum van het kwartaal hebben in plaats van de eerste datum van het volgende kwartaal.
Haal het Excel-bestand op
Conclusie
Door simpelweg functies te combineren, kunt u een eenvoudige formule maken om een kwartaal vanaf een datum te berekenen. En in dit artikel hebben we hiervoor vier formules + VBA-code onderzocht.
Eerlijk gezegd is nr. 1 de meest gebruikte en gemakkelijkste manier om een kwartaaldatum te vinden, maar je kunt de rest gebruiken als je wilt. Ik hoop dat je dit artikel nuttig vond, maar vertel me nu dit.
Kent u een andere methode om het kwartaalnummer uit een datum te berekenen?
Deel het met mij in het opmerkingengedeelte. Ik hoor graag van je, en vergeet niet deze tip met je vrienden te delen. Ik weet zeker dat ze het zullen waarderen.