[Excel] - Pivot Table met een niet standaard tijdseenheid

[Tijntje]

In Excel 2016 heb ik een kleine uren administratie in elkaar gezet voor mijn vrouw. In de eerste sheet is een overzicht van de gemaakte uren.


Op de tweede sheet staat een Pivot Table zodat ze makkelijk de facturen kan uitsturen.


Nu moet ze tegenwoordig haar uren op de 25^e van de maand doorgeven. Nu ben ik aan het zoeken of je in excel ook de Pivot Table grouping kan aanpassen op custom tijdseenheden, om op die manier weer een makkelijk overzicht te creëren. Dus van 26^e van de vorige maand tot en met de 25^e van de huidige maand.

[treslem]

Ik zou een extra kolom erbij zetten in de data sheet met een periode (wel of niet automatisch vullen) en deze dan in de pivot meenemen.

Kleine moeite, groot plezier.

[Tijntje]

treslem schreef op zaterdag 1 juli 2017 @ 17:13:
Ik zou een extra kolom erbij zetten in de data sheet met een periode (wel of niet automatisch vullen) en deze dan in de pivot meenemen.

Kleine moeite, groot plezier.

Ik denk dat ik begrijp wat je bedoeld maar kun je het iets verder uitleggen. Hoe kan ik een automatisch periode in de data sheet aan de hand van de ingegeven datum aanmaken? De ingevulde data zijn namelijk redelijk random.

[treslem]

Een if then else statement in de kolom periode zetten.

bv

if(dag van datm > 26, maand+1,maand van datum)

maar dan in een echte formule (zit op mijn Ipad zonder Excel)

[Tijntje]

treslem schreef op zaterdag 1 juli 2017 @ 23:25:
Een if then else statement in de kolom periode zetten.

bv

if(dag van datm > 26, maand+1,maand van datum)

maar dan in een echte formule (zit op mijn Ipad zonder Excel)

Als je in de gelegenheid bent een daadwerkelijke formule te maken zou ik je heel dankbaar zijn . Ben deze hele ochtend aan het aanklooien maar kom er nog niet uit.

[Lustucru]

Nou loop je lang genoeg op tweakers rond om te weten dat we dan om te beginnen heel graag willen weten wat je dan wél hebt bereikt in die ochtend klooien. Je zult vast wel *iets* van een formule hebben bedacht, al is het maar '='. Post dat even.

[Tijntje]

Lustucru schreef op maandag 3 juli 2017 @ 17:16:
Nou loop je lang genoeg op tweakers rond om te weten dat we dan om te beginnen heel graag willen weten wat je dan wél hebt bereikt in die ochtend klooien. Je zult vast wel *iets* van een formule hebben bedacht, al is het maar '='. Post dat even.

Daar heb je gelijk in

code:

=IF(DAY([@Datum])<=25;MONTH([@Datum]);MONTH([@Datum])+1)

Heb nu dit en dat geeft in ieder geval een maand nummer terug dat redelijk lijkt te kloppen met wat ik wil echter wil ik graag dat het de volledige maand naam uit spuigt.

[_WouterB]

Tijntje schreef op maandag 3 juli 2017 @ 17:21:
[...]

Daar heb je gelijk in

code:

=IF(DAY([@Datum])<=25;MONTH([@Datum]);MONTH([@Datum])+1)

Heb nu dit en dat geeft in ieder geval een maand nummer terug dat redelijk lijkt te kloppen met wat ik wil echter wil ik graag dat het de volledige maand naam uit spuigt.

https://exceljet.net/formula/get-month-name-from-date

[Tijntje]

_WouterB schreef op maandag 3 juli 2017 @ 17:22:
[...]


https://exceljet.net/formula/get-month-name-from-date

Maar ik krijg enkel het cijfer van de maand nu terug en met die functies kan ik dat niet omzetten.

[ Voor 8% gewijzigd door Tijntje op 03-07-2017 18:03 ]

[_WouterB]

Tijntje schreef op maandag 3 juli 2017 @ 18:01:
[...]

Maar ik krijg enkel het cijfer van de maand nu terug en met die functies kan ik dat niet omzetten.

met deze formule toch wel?

code:

 =CHOOSE(--maand nummer--,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Een andere mogelijkheid is de switch functie: https://support.office.co...ce-4530-8a45-d532ec4aa25e

[Tijntje]

_WouterB schreef op maandag 3 juli 2017 @ 18:04:
[...]


met deze formule toch wel?

code:

 =CHOOSE(--maand nummer--,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Een andere mogelijkheid is de switch functie: https://support.office.co...ce-4530-8a45-d532ec4aa25e

Kan ik dat ook op een of andere manier dat ook direct meenemen in die cel waar ik nu de formule al voor gebruik?

[_WouterB]

Tijntje schreef op maandag 3 juli 2017 @ 18:06:
[...]

Kan ik dat ook op een of andere manier dat ook direct meenemen in die cel waar ik nu de formule al voor gebruik?

Ja, gewoon de formules samenvoegen?

code:

 =CHOOSE(IF(DAY([@Datum])<=25;MONTH([@Datum]);MONTH([@Datum])+1);"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

(dit heb ik zelf niet getest...)

[Tijntje]

_WouterB schreef op maandag 3 juli 2017 @ 18:07:
[...]

Ja, gewoon de formules samenvoegen?

code:

 =CHOOSE(IF(DAY([@Datum])<=25;MONTH([@Datum]);MONTH([@Datum])+1);"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

(dit heb ik zelf niet getest...)

Dat lijkt niet helemaal te werken
In de formule scherm werkt het


Echter in de sheet zelf krijg ik alleen de TRUE te zien.


Voor mijn gevoel ben ik er heel dicht bij maar heb nog even het laatste zetje nodig.

[_WouterB]

Tijntje schreef op maandag 3 juli 2017 @ 19:33:
[...]

Dat lijkt niet helemaal te werken
In de formule scherm werkt het
[afbeelding]

Echter in de sheet zelf krijg ik alleen de TRUE te zien.
[afbeelding]

Voor mijn gevoel ben ik er heel dicht bij maar heb nog even het laatste zetje nodig.

Probeer eens om de '@'tekens uit je formule te halen, ik denk dat die het probleem vormen.
Bij mij werkt zowel

code:

 =CHOOSE(IF(DAY(A1)<=25;MONTH(A1);MONTH(A1)+1);"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

als

code:

=CHOOSE(IF(DAY([Datum])>25;MONTH([Datum])+1;MONTH([Datum]));"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

[treslem]

zonder je helemaal in verwarring te willen brengen, een vlookup doet ook wonderen

1. januari
2 februari
in aparte kolommen in bv. een apart data tabje..

[Tijntje]

_WouterB schreef op maandag 3 juli 2017 @ 19:46:
[...]


Probeer eens om de '@'tekens uit je formule te halen, ik denk dat die het probleem vormen.
Bij mij werkt zowel

code:

 =CHOOSE(IF(DAY(A1)<=25;MONTH(A1);MONTH(A1)+1);"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

als

code:

=CHOOSE(IF(DAY([Datum])>25;MONTH([Datum])+1;MONTH([Datum]));"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

Hij blijft bij mij enkel de waarde TRUE terug geven.

[loeberce]

Heb je deze formules letterlijk overgenomen? Heb je NL of UK Excel? Dit kan uitmaken omdat je soms met komma's als formule scheidingsteken werkt en soms niet.

Wat gebeurt er als je de formule evalueert?

[breew]

je kunt ook een datum/nummer omzetten naar een maand via de tekstweergave:
A1 = 1-1-2017
B1 = TEXT(A1;"mmmm") = januari
B1 = TEXT(A1;"mmm") = jan
B1 = TEXT(A1;"mm") = 01

etc....

[ Voor 24% gewijzigd door breew op 04-07-2017 11:10 ]

[_WouterB]

Tijntje schreef op dinsdag 4 juli 2017 @ 10:54:
[...]

Hij blijft bij mij enkel de waarde TRUE terug geven.

Dat snap ik niet goed... kopieer en plak die formule eens naar een nieuwe cel (dus niet dat formulescherm gebruiken) en wijzig dan de A1 naar de cell waar je datum staat (dus niet proberen om naar die kolom 'Datum' te verwijzen).

Als dat niet werkt moet je de formules gaan opsplitsen om te kijken waar het probleem zit. Als het probleem gevonden is kan je dan alles weer samenvoegen zoals je graag zou hebben. In de volgende formules ga ik er van uit dat er een datum in cel A2 staat. Doe dit ook eens in een nieuw werkblad en niet in de tabel layout die je nu gebruikt voor de administratie (om zo veel mogelijk dingen uit te kunnen sluiten).

1. Vul een datum in in cel A2
2. In cel B2 reken je het nummer van de maand uit
   
   code:
   

   =IF(DAY(A2)<=25;MONTH(A2);MONTH(A2)+1)

3. In cel C2 bepaal je de naam van de maand aan de hand van het resultaat in cel B2
   
   code:
   

   =CHOOSE(B2;"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

@loeberce : ik denk dat als daar iets fout mee zou zijn dat de functie niet wordt geëvalueerd en er #error ofzoiets in de cel komt te staan.
@breew : op die manier kan de aangepaste maandsplitsing niet gemaakt worden.

[Tijntje]

loeberce schreef op dinsdag 4 juli 2017 @ 11:08:
Heb je deze formules letterlijk overgenomen? Heb je NL of UK Excel? Dit kan uitmaken omdat je soms met komma's als formule scheidingsteken werkt en soms niet.

Wat gebeurt er als je de formule evalueert?

Heb zowel mijn eigen formule aangepast als ook de formule rechtstreeks overgenomen. In mijn Excel is ook de ; het scheidingsteken.

Bij een evaluatie geeft hij geen fout. Ik kan later vandaag wel eens kijken of ik een lege sheet kan maken met de formule en die eventueel uploaden.

[loeberce]

Als er TRUE komt te staan eindigt de IF-functie voortijdig en is er dus daar iets fout. Via evalueren kan je alle stappen doorlopen en zie je dus wanneer de formule ophoudt. Vaak komt dit door een haakje sluiten op de verkeerde plek.

[_WouterB]

loeberce schreef op dinsdag 4 juli 2017 @ 11:16:
Als er TRUE komt te staan eindigt de IF-functie voortijdig en is er dus daar iets fout. Via evalueren kan je alle stappen doorlopen en zie je dus wanneer de formule ophoudt. Vaak komt dit door een haakje sluiten op de verkeerde plek.

Ook weer wat bijgeleerd vandaag, dat evalueren kende ik nog niet
@Tijntje : cel selecteren, daarna naar het tablad 'formulas' gaan en in het vakje 'formula auditing' kies je voor 'evaluate formula'.

[Tijntje]

Op een andere machine waar ik geen Dutch Language Pack over Office 2016 heb geïnstalleerd werkt het wel.
Het lijkt er dus op dat het daar ergens mis gaat ondanks dat de formule geen syntax errors geeft.
Ik heb pas vanavond weer toegang tot de machine met de Language Pack om verder te testen.
Tot zover dank voor al jullie hulp

[ Voor 7% gewijzigd door Tijntje op 04-07-2017 11:26 ]

[Tijntje]

Bedank alle, het lijkt erop dat hij twee keer de formule had aangemaakt.


Met die Evaluate Formula had ik dat er zo uit.

EDIT
@_WouterB
Loop ik toch nog tegen een probleem aan, alle entries vanaf de 26^e in december in het jaar geven een error aangezien dan het cijfer 13 wordt terug gegeven in plaats van weer 1.

[ Voor 43% gewijzigd door Tijntje op 04-07-2017 17:27 ]

[_WouterB]

Tijntje schreef op dinsdag 4 juli 2017 @ 16:36:
Bedank alle, het lijkt erop dat hij twee keer de formule had aangemaakt.
[afbeelding]

Met die Evaluate Formula had ik dat er zo uit.

EDIT
@_WouterB
Loop ik toch nog tegen een probleem aan, alle entries vanaf de 26^e in december in het jaar geven een error aangezien dan het cijfer 13 wordt terug gegeven in plaats van weer 1.
[afbeelding]

Haha, ja dat is een gevolg van maand+1 te doen Een extra ifje moet dit wel kunnen oplossen:
=CHOOSE(IF(DAY(A1)<=25;MONTH(A1);if(MONTH(A1)+1>12;1;MONTH(A1)+1));"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

Wel even opletten of de dag waarop de nieuwe maand start nu nog goed ligt, want ik zie in je screenshot dat je nu <=24 gebruikt ipv 25

Edit: En je zal A1 ook moeten vervangen door [Datum Les] natuurlijk...

[ Voor 3% gewijzigd door _WouterB op 04-07-2017 17:44 ]

[Tijntje]

_WouterB schreef op dinsdag 4 juli 2017 @ 17:33:
[...]


Haha, ja dat is een gevolg van maand+1 te doen Een extra ifje moet dit wel kunnen oplossen:
=CHOOSE(IF(DAY(A1)<=25;MONTH(A1);if(MONTH(A1)+1>12;1;MONTH(A1)+1));"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec")

Wel even opletten of de dag waarop de nieuwe maand start nu nog goed ligt, want ik zie in je screenshot dat je nu <=24 gebruikt ipv 25

Edit: En je zal A1 ook moeten vervangen door [Datum Les] natuurlijk...

Topperrrr

Die 24 klopt inderdaad het moet tot en met de 24^e in de huidige maand en vanaf de 25^e in de volgende.

EDIT
Nu werkt zoals bedoeld zou het dan ook mogelijk zijn om die maanden in Excel daadwerkelijk als maanden weer te geven in plaatst van gewone tekst?
Nu staan de maanden wat random door elkaar heen in het filter veld.


Buiten dat loop ik tegen een ander probleem aan dat bijvoorbeeld een les op 26 december 2016 geteld zou moeten worden bij Januari van 2017. In de Pivot Table is natuurlijk de Group op basis van de tekst van kolom Factuur Perdiode en die wordt dan weer op jaar op gesplitst.



Nu wordt die 100 uur die ik heb ingevuld bij geschreven in het jaar 2016 terwijl dat 2017 zou moeten worden.


GRRR, hoe dichter bij ik bij de oplossing kom hoe lastiger het lijkt te worden

[ Voor 37% gewijzigd door Tijntje op 04-07-2017 18:11 ]

[_WouterB]

Tijntje schreef op dinsdag 4 juli 2017 @ 17:47:
[...]

Topperrrr

Die 24 klopt inderdaad het moet tot en met de 24^e in de huidige maand en vanaf de 25^e in de volgende.

Nu werkt zoals bedoeld zou het dan ook mogelijk zijn om die maanden in Excel daadwerkelijk als maanden weer te geven in plaatst van gewone tekst?
Nu staan de maanden wat random door elkaar heen in het filter veld.
[afbeelding]

Ik kan je hier niet echt mee helpen want in mijn Excel (2013) sorteert hij gewoon op maand volgorde ipv alfabetisch (en als ik z->a selecteer gaat het van december->januari).
De gemakkelijkste oplossing lijkt mij om je maand namen te veranderen naar '01-Januari' '02-februari' etc

[_WouterB]

Tijntje schreef op dinsdag 4 juli 2017 @ 17:47:
[...]

GRRR, hoe dichter bij ik bij de oplossing kom hoe lastiger het lijkt te worden

vul in je de kolom waar je de maand wil hebben eens het volgende in:

code:

=DATE(YEAR(A2);IF(DAY(A2)<=24;MONTH(A2);MONTH(A2)+1);1)

En pas daarna de opmaak in die cel aan als volgt: rechtermuisknop -> 'format cells' -> custom -> bij type vul je in 'jjjj - mmmm'.


Dit is een veel elegantere oplossing dan alles wat hiervoor al geprutst is In je draaitabel kan je dan ook filteren op jaartallen en sorteren op maanden enzo, want het zijn allemaal data

[Tijntje]

_WouterB schreef op dinsdag 4 juli 2017 @ 18:40:
[...]


vul in je de kolom waar je de maand wil hebben eens het volgende in:

code:

=DATE(YEAR(A2);IF(DAY(A2)<=24;MONTH(A2);MONTH(A2)+1);1)

En pas daarna de opmaak in die cel aan als volgt: rechtermuisknop -> 'format cells' -> custom -> bij type vul je in 'jjjj - mmmm'.
[afbeelding]

Dit is een veel elegantere oplossing dan alles wat hiervoor al geprutst is In je draaitabel kan je dan ook filteren op jaartallen en sorteren op maanden enzo, want het zijn allemaal data

Ik zat inderdaad met het zelfde idee te klooien in mijn hoofd maar kreeg het nog niet voor elkaar.

Volgens mij is het nu helemaal gefixed.
Dank je voor je geduld en hulp, ik waardeer het enorm.

[ Voor 3% gewijzigd door Tijntje op 04-07-2017 18:49 ]