Weeknummers met de calculated kolom in SharePoint
In SharePoint kun je met de calculated kolom (berekende kolom) een berekening doen op basis van een andere kolom. De syntax van de formule lijkt op die van Excel. Met de calculated kolom zijn leuke dingen te doen. Zo ook het bepalen van een weeknummer op basis van een datum uit een andere kolom. In deze post leer je wat de “best practice” is voor het weergeven van weeknummers en krijg je gelijk een paar handigheidjes mee voor het werken met de calculated kolom.
Weergeven van een Jaartal, Maand, Dag
Met de formule YEAR() haal je uit een datum het jaartal op. Als je dit doet krijgt het jaartal in de kolom een punt op het duizendtal, om dit te voorkomen moet je de formule uitbreiden met TEXT() en een nul. Het maakt niet uit of je één, twee, drie of vier nullen opgeeft. Het resultaat is hetzelfde. Als je vijf nullen opgeeft komt er voor het jaartal een nul. De maanden worden gelijk goed weergegeven. Als je voor een datum altijd twee posities nodig hebt kun je voor de maand hetzelfde doen.
| 2.012 | =YEAR([Modified]) |
| 2012 | =TEXT(YEAR([Modified]),”0″) |
| 2012 | =TEXT(YEAR([Modified]),”00″) |
| 2012 | =TEXT(YEAR([Modified]),”000″) |
| 2012 | =TEXT(YEAR([Modified]),”0000″) |
| 02012 | =TEXT(YEAR([Modified]),”00000″) |
Als je met de formule MONTH() de maand uit een datum haalt krijg je een getal. Met de formule CHOOSE() kun je het getal vervangen met eigen tekst:
=CHOOSE(MONTH(Modified),”January”,”February”,”March”,”April”,”May”,”June”,”July”,”August”,”September”,”October”,”November”,”December”)
Met de formule TEXT() kun je ook de namen uit een datum halen. Je krijgt dan de namen in de taal terug van de omgeving:
| Formule | Resultaat |
|---|---|
| =TEXT([Modified],”yy”) | 12 |
| =TEXT([Modified],”yyyy”) | 2012 |
| =TEXT([Modified],”m”) | 2 |
| =TEXT([Modified],”mm”) | 02 |
| =TEXT([Modified],”mmm”) | Feb |
| =TEXT([Modified],”mmmm”) | February |
| =TEXT([Modified],”dd”) | 16 |
| =TEXT([Modified],”dddd”) | Thursday |
Welke methode gebruik je om een weeknummer weer te geven
Standaard is er niet zoals in Excel een functie die gebruikt kan worden om een weeknummer uit een datum te halen. De formule =INT(([Modified]-DATE(YEAR([Modified]),1,1))/7 lijkt te werken. Maar geeft geen weeknummer voor 1 januari.
Als de week start op zondag kun je de formule gebruiken:
=If(Int(([Modified]-Date(Year([Modified]),1,1)+(TEXT(Weekday(Date(Year([Modified]),1,1)-1),”d”)))/7)=0,52,Int(([Modified]-Date(Year([Modified]),1,1)+(TEXT(Weekday(Date(Year([Modified]),1,1)-1),”d”)))/7))
Als de week op maandag start kun je de formule gebruiken:
=If(Int(([Modified]-Date(Year([Modified]),1,1)+(TEXT(Weekday(Date(Year([Modified]),1,1)-2),”d”)))/7)=0,53,Int(([Modified]-Date(Year([Modified]),1,1)+(TEXT(Weekday(Date(Year([Modified]),1,1)-2),”d”)))/7))
Deze formules heb ik in Excel gesimuleerd om het verschil visueel te maken zodat je goed kunt zien wat werkt en wat niet. In onderstaande afbeelding staat bovenaan de datums van de zondagen in volgorde van tijd. Oranje is de datum van nu, in de formule [Modified]. Op de regels A, B, C, D en E is de formule die rechts in dezelfde regel staat toegepast op de horizontale datums van die bovenaan staan.
Zoals al hiervoor gemeld geeft optie A niet het gewenste resultaat. Optie B werkt maar loopt een week voor met wat in het algemeen in gebruik is voor het weeknummer. Optie C geeft het zelfde als optie B. Optie D komt dichter in de buurt, alleen bij de laatste week van het jaar sprint het weeknummer vaak naar 53.
Optie E geeft de formule weer om het weeknummer volgens de ISO weer te geven. Deze norm zegt dat de eerste week van het jaar de week is met een maandag, dat vertaald zich naar de week waarin vier of meer dagen van het nieuwe jaar in de week vallen. Optie E is ook gelijk de methode die in algemeen gebruik geduid wordt als de juiste methode voor het bepalen van het weeknummer:
=INT(([Modified]-DATE(YEAR([Modified]-WEEKDAY([Modified]-1)+4),1,3)+WEEKDAY(DATE(YEAR([Modified]-WEEKDAY([Modified]-1)+4),1,3))+5)/7)
Pas op met de “Quick and dirty trick” [Today]
Tot slot nog de toegift: De grote vergissing die ik regelmatig voorbij zie komen!
Het gebruik van [Today] in een calculated kolom. [Today] heeft niet het effect om de datum van vandaag te tonen maar laat de datum zien wanneer het item voor de laatste keer is opgeslagen. De datum wordt natuurlijk alleen ververst als het item wordt bewerkt en opgeslagen.De Today-truc is dus het zelfde als de kolom ‘Gewijzigd’.
