Handigheidjes met datums in Excel 2013

Excel | Handigheidjes met datums in Excel 2013

Datums in Excel, wat kan je er mee? Excel kent een paar leuke functies waar wellicht niet iedereen van op de hoogte is. Want als je opzoek bent naar een datum of periode dan tik je deze toch gewoon in? Probleem opgelost zou je zeggen. Vaak werkt het zo, maar zeker niet altijd. Ik werk heel vaak met Excel in combinatie met Jet reports (een BI add-on voor Excel). Door deze Add-on ben ik instaat om geautomatiseerd data op te halen vanuit verschillende databases, hierdoor kan je vaak niet werken met een vaste periode of datum maar moet deze variabel zijn. Een voorbeeld hiervan is, de laatste 2 weken, de laatste 2 uur of misschien wel de datum van de laatste zondag? Hoe bereken je dat? Zoals voor bijna alles geldt, als je het weet is het makkelijk. Dat is niet anders voor werken met datums in Excel.

Werken met datums kan op een aantal manieren:

  • Alleen visueel, door middel van opmaak,
  • of door middel van formules

Beide manieren hebben voor en nadelen en je zult zelf moeten bepalen wat in jouw geval de beste manier is. In dit artikel licht ik alleen de manier met formules toe.

 


Datum van vandaag automatisch tonen

Dit is een makkelijke. Type in een willekeurige cell de functie =TODAY() of in het Nederlands =VANDAAG. Dan zul je zien dat de datum van vandaag wordt getoond in de cel. Let op: de datum die wordt getoond is de datum van de computer op dat moment. Staat je datum verkeerd ingesteld op je computer, dan zal vanzelfsprekend de datum in Excel ook niet juist zijn.
Wat ook kan is de datum en tijd van het moment tonen. Dit kan door de functie =NOW() of in het Nederlands =NU(). 

Nog even kort:

  • =VANDAAG()
    • =TODAY()
  • =NU()
    • =NOW()

Als je deze functie goed kent dan is dat de basis van waarmee wij gaan werken. Op naar de volgende!

 


De dag van de week of het weeknummer tonen

Het tonen van het weekdag of week nummer kan met:

  • WEEKDAG()
    • WEEKDAY()
  • WEEKNUMMER()
    • WEEKNUMBER()

 


De huidige maand, dag of jaar tonen

Het berekenen van bijvoorbeeld het nummer van de huidige dag/maand/jaar werkt met onderstaande functies/formules:

  • DAG()
    • DAY()
  • MAAND()
    • MONTH()
  • JAAR()
    • YEAR()

Zoals je ziet, het spreekt voorzich. Maar hoe werkt het? Net zo makkelijk, zie hieronder:

Je kan het dus met een formule doen maar ook door hard een datum te typen of te verwijzen naar een Cel. Het resultaat van de 2de functie zou dan dus 15 moeten zijn.

 

Je kan het dus met een formule doen maar ook door hard een datum te typen of te verwijzen naar een Cel. Het resultaat van de 2de functie zou dan dus 03 moeten zijn.

 

Je kan het dus met een formule doen maar ook door hard een datum te typen of te verwijzen naar een Cel. Het resultaat van de 2de functie zou dan dus 2016 moeten zijn.

 


De laatste zondag berekenen

Het berekenen van de laatste zondag kan je doen met behulp van eerder genoemde functies / formules. Namelijk:

De +1 aan het einde zorgt ervoor dat er 1 dag wordt opgeteld, want de laaste zondag is natuurlijk niet het zelfde als de datum van vandaag minus het dagnummer van de week. Dan kom je namelijk uit op de datum van afgelopen zaterdag.

 


De dag van morgen berekenen

Een soortgelijk truukje kan je doen om bijvoorbeeld de dag van morgen te berekenen.

De +1 aan het einde zorgt ervoor dat er 1 dag wordt opgeteld. Wil je de weten wat de datum is over 78 dagen? Dan vul je aan het einde gewoon +78 in. Wil je weten wat de datum 55 dagen geleden was? Dan vul je aan het einde gewoon -55 in. Was alles maar zo simpel!

 


Het eind van de maand berekenen

Een ander handigheidje is om het eind van de maand of die van vorige maand te berekenen. Welke maand je ook wilt, het maakt niet uit. Deze functie berekend de laatste datum van iedere willekeurige maand. Dat gaat zo:

De 0 aan het eind van de functie geeft aan hoeveel maanden er moet worden geschoven. Met andere woorden, wil je de einddatum van de huidige maand, zoals in het voorbeeld, dan vul je 0 in. Wil je eind datum van de volgende maand? Dan vul je daar een 1 in en zo verder. Je kunt het zo gek maken als je zelf wilt. 🙂

Zoals je ziet kan je ook een harde datum in typen of een celverwijziging doen. Je ziet dan dat in dit geval (18-03-2016) dat de uitkomst 31-03-2016 is. Handig he!

 


Het begin van de maand berekenen

Als je weet hoe je het eind van de maand kan berekenen kan je ook het begin van de maand uitrekenen, met dezelfde functie. Stel je wilt van deze huidige maand de begin datum berekenen. Dat kan dan zo:

De -1 aan het eind van de functie geeft aan hoeveel maanden er moet worden geschoven. Met andere woorden, wil je de begindatum van de huidige maand berekenen, zoals in het voorbeeld, dan vul je -1 in. Nu heb je de eind datum van de vorige maand berekend. Tel er 1 bij op en je hebt de begin datum van de huidige maand. Hatsikidee!

Zoals je ziet kan je ook een harde datum in typen of een celverwijziging doen. Je ziet dan dat in dit geval (18-03-2016) de uitkomst 01-03-2016 is.

 


Het eind van het huidige jaar berekenen

Als je weet hoe je het eind van de maand kan berekenen kan je ook het eind van het jaar uitrekenen. Stel je wilt van dit huidige jaar de laatste dag van het jaar uitrekenen. Dat gaat dan als volgt:

 

Bovenstaande formule maakt gebruik van meerdere functies zoals je ziet. Namelijk LAATSTE.DAG(), VANDAAG() en de MAAND() functie. Als je deze 3 combineert met elkaar dan kun je het eind van het huidige jaar, en ieder ander jaar, automatisch uitrekenen. Omdat deze formule iets lastiger is dan de voorgaande leg ik deze nog even uit.

  • Allereerst wordt er het eind van de maand berekend op basis van de datum van vandaag (of een willekeurige andere datum)
  • Vervolgens geef je aan hoeveel maanden er verschoven moeten worden.
    • Vul voor het gemak anders eerst 0 in. Dan krijg je de laatste datum van de huidige of gekozen maand.
  • Nu gaan wij uitrekenen hoeveel maanden het duurt (vanaf de datum van vandaag) totdat de laatste maand van het jaar is bereikt.
    • Op basis van de huidige datum kunnen wij uitrekenen welke maand de huidige is, dit doen wij met de MAAND() en VANDAAG() functie. Afhankelijk van welke maand wij nu in leven kan dit dus iedere keer anders zijn. Nu leven wij in maand 3 (Maart).
    • Het mag geen verrassing zijn dat ieder willekeurig jaar, 12 maanden heeft, hier maken wij dan ook gebruik van door 12 maanden min 3 uit te rekenen, 9 dus.
    • Nu weten wij dat er nog 9 maanden tegaan zijn tot de laatste maand van het jaar is bereikt.
    • Vervolgens vullen wij deze functie MAAND(VANDAAG()) in in de functie LAATSTE.DAG() op de plek waar je eerder een 0 hebt neergezet.
    • Nu staat er dus eigenlijk, LAATSTE.DAG(VANDAAG();9), wat zich vertaald naar 31.12.XX.

Deze is lastiger he?

Als het niet duidelijk is wat ik bedoel lees dan nog even het stukje hierboven over het berekenen van het eind van de maand.

 


Het aantal dagen tot het eind van het jaar berekenen

Stel je wilt weten hoeveel dagen het nog duurt tot dat het eind van het jaar is bereikt, op basis van de datum van vandaag. Deze lijkt misschien lastig, maar is in feite een kleine uitbreiding op de formule van hiervoor. 🙂

Zodra je weet welke datum de laatste datum van het jaar is, kan je ook uitrekenen hoeveel dag het nog duurt tot het zover is gebaseerd op de datum van vandaag. Het enige wat je toevoegt aan de formule van hiervoor is: -VANDAAG().

Nu worden wordt er een getal getoond, dit zijn het aantal dagen tot en met het eind van het jaar. Je kan het ook proberen met een zelf ingevulde datum, zie hieronder.

Viel best mee toch?

 


 

Wil je al dit gegoochel met datum’s ‘in het echt’ zien? Download dan eens onderstaand bestand. Daar zie je alles nog een keer op een rij.

Wil je toch nog wat weten maar staat de oplossing hier niet bij? Laat dan even een berichtje achter of plaats een bericht op het forum.


3 Reacties

  1. […] Het opschonen van datums of andere tijdseenheden is ook iets wat je moet kunnen. Hier heb ik eerder een interessant stuk over geschreven. Meer daarover lees je hier: Handigheidjes met datums in Excel 2013. […]

  2. […] Handigheidjes met datums in Excel 2013 […]

  3. connectingworks
    | Beantwoorden

    super Danny, heel leuk. Ga ik zeker gebruiken.

Heb je nog opmerkingen of vragen? Je kunt ze in de box hieronder of op het forum kwijt.