Datumkalkylering II

Kvartal

För att returnera vilket kvartal ett datum tillhör skapas förutsättningarna i två steg:

Exemplet i tabellen nedan beskriver kortfattat den tekniska lösningen. Formeln extraherar aktuell månad ur datumet och därefter letar den upp det matchande kvartalet i konstanten "Kvartal".

 

 

För att ovanstående formel ska fungera korrekt i version 2000 måste "pipe"-tecknet | ersättas med "backslash"-tecknet, dvs med "\".

Ett betydligare enklare sätt är att använda sig av följande formel:

Arbetsdagar

Vid installation av tillägget "Analysis Toolpak" får man bl a tillgång till funktionen NETTOARBETSDAGAR. M h a denna funktion kan antal arbetsdagar per period erhållas, även om perioden innehåller allmänna helgdagar. Den kan väl komma till pass vid t ex personalstatistik. I tabellen nedan visas ett exempel på funktionen.

 

 

För att beakta helgdagar måste det anges i särskild ordning. Ett tips är att ange dessa separat i ett kalkylblad och referera till de celler som innehåller datumen för helgdagarna såsom:

 

Har man inte tillgång till "Analysis Toolpak" så kan man ändå få fram antal arbetsdagar mellan två datum mha följande formel:

En kortare matrisformel är:


För att skapa datumserier för arbetsdagar, måndag till fredag, krävs en formel som exkluderar lördagar och söndagar. Självklart (!) kan man även lösa det m h a inbyggda funktioner i XL. Målcellerna måste dock formateras med datumformat:

Antal dagar per månad

Ibland kan det vara aktuellt att erhålla antal dagar per månad. Det finns ingen inbyggd funktion utan man måste använda ett flertal datumfunktioner till det. Tabellen nedan visar ett exempel.

 

 

ÅR-funktionen returnerar 1999 i exemplet medan MÅNAD returnerar 1. DATUM-funktionen returnerar sista datumet för månaden m h a addition med konstanten 1. DAG-funktionens värde är följaktligen 1999-01-31 och returnerar således 31.

Räkna differenser i år, månader veckor eller dagar

XL innehåller många funktioner, även odokumenterade sådana! Bl a är funktionen DATEDIF odokumenterad (dock finns den dokumenterad i XL 2000). Den räknar ut skillnaden mellan två datum, antingen i termer av år, månader eller dagar.

 

 

I tabellen ovan visas de tre möjliga utfallen funktionen ger. "D" står för dag, "M" för månad och "Y" för år.

Utöver dessa tre basargument kan de även kombineras! Följande kombinationer är möjliga och exemplifieras i nedanstående tabell:

 

Vill man i en och samma cell få fram skillnaden i år, månader och dagar så löser följande formel det:

DATEDIF-funktionen, såsom den visas i ovanstående exempel, är inte alltid exakt. För nedanstående exempel erhålls 11 månaders skillnad. Utvecklas funktionen kan bättre precision erhållas. I tabellen nedan visas en lösning och där resultatet blir 11,30, dvs 11 månader och 30 dagars differens. En enklare beräkning kan också göras mha faktorn
365.25, vilket ger resultat 11,99, dvs 12 månader. För skottår bör faktorn justeras till 366.

 

 

För att erhålla antal veckor mellan två datum behövs inte någon avancerad formel utan följande enkla formel löser uppgiften. 

Vill man också fram antal dagar utöver veckantalet sker det bäst med:

Se också Nedräkning!

Första & sista dagen i månaden

För att räkna fram sista dagen i varje månad måste vi dels ge XL ett startdatum samt använda oss av flera datumfunktioner. I tabellen nedan visas ett exempel. Genom addition erhålls månad 2 (Månad 1 + 1) men då variabeln "DAG" är lika med "0" så räknar XL fram den sista dagen i föregående månad (Januari) istället för den första dagen i månaden därefter (Februari). Knepigt? Pröva att skriva in formeln =DATUM(ÅR(B2);MÅNAD(B2)+1;1) - resultatet blir då 1999-02-01. Tar vi bort additionen i den ursprungliga formeln så erhålls istället 1998-12-31!

 

I nästa exempel visas hur vi beräknar fram första dagen i månaden. Formelmässigt bygger exemplena vidare på föregående resonemang.

 

 

Har du Analysis Toolpak installerad kan du använda dig av funktionen SLUTMÅNAD.

Antag att du har startdatumet 2000-05-24 och vill erhålla

 

Skapa egna datumintervaller

I vissa sammanhang, såsom vid uppföljning och vid rapportering , kan man vara betjänt utav att skapa egna datumintervaller. Exemplet i tabellen nedan visar ett sätt att bygga upp datumintervaller. Den utgår från samma grundformel som i föregående exempel. Här används också KOLUMN-formeln för att få fram formelns kolumnposition (t ex 2) och som reduceras med 1 (2 - 1 ) och som därefter läggs till månad, dvs lägger till ytterligare en månad (t ex månad 1+1=månad 2). 

 

Första måndagen i månaden

Söndagar är dag 1 i veckan för XL, dvs representeras av talet 1. Måndagar representeras följaktligen av talet 2. Givet denna kunskap kan vi bygga upp en formel som beräknar datumet för den första måndagen i en månad. Bilden nedan visar formeln i sin helhet. OM-funktionen används för att utvärdera huruvida startdatumets dag är en söndag eller måndag och beroende utfallet sker en beräkning. 

 

 

Vill man lösa det på annat sätt sker det bäst med följande formel:

Skottår?

Skottår inträffar var 4:e år, dvs årets dagar blir 366 istället för 365. Men hur får fram om ett år är skottår eller inte? Som vanligt i XL så finns det flera lösningar på problem. För att bestämma om ett år är skottår eller inte kan man vanligtvis nöja sig med att undersöka huruvida årtalet är jämnt delbart med 4. Om ja, så är det ett skottår. Vilken månad är det som får en extra dag? Jo, februari månad får en dag till, dvs 29 dagar istället för 28 dagar. Båda dessa resonemang kan "översättas" till XL för att i XL fastställa huruvida år är skottår eller inte. I det första exemplet nedan utvärderas det aktuella året genom ett påstående om att utfallet av REST-funktionen. I det andra exemplet sker också en utvärdering i kombination med ett flertal datumfunktioner. 

 

Ett mycket enklare sätt att utvärdera huruvida ett år är skottår eller inte är att använda sig av följande formel:

 

Vill man få fram antal skottår för perioder kan det ske enklast i två steg:

En mer komplex lösning är att använda sig av följande formel:

Ovanstående formel ger enbart antal skottår mellan två datum. Om man vill beakta vilka datum som anges och utifrån dessa erhålla antal skottår kan det lösas på nedanstående sätt:

Antag att cellen A1 innehåller 1990-02-10 och cell A2 1996-02-12 så blir antal skottår är 1, då cellvärdet i A2 är innan skottdagen (29). Ändras värdet i A2 till 1999-02-29 så blir antalet skottår 2 för denna formel.

Datumkonvertering

Här demonstreras hur OM-funktionen i kombination med TEXT- och DATUM-funktioner kan lösa ett komplext problem.

Utmaningen är att omvandla datum till löpande månadsnummer och med följande förutsättningar:

 

I tabellen nedan visas exemplet i sin helhet.

Formeln som genererar utfallet är:

Puh! Vad gör formeln egentligen? Jo, följande:

 

För vidare diskussion kring OM-funktionen se Villkorsstyrd kalkylering.

Funktionen DATUMVÄRDE

Om du t ex arbetar med importerad data från en annan miljö kan det uppstå problem med datum, i synnerhet om man ska utföra datumberäkningar. 

Det är här som funktionen DATUMVÄRDE kommer in i bilden.

Antag att du har datumangivelser som ser ut på följande sätt: 20000515. 

För att erhålla ett konverterat datum, såsom 2000-05-15, kan följande formel ge lösningen.


Resultatet visas som ett heltal, såsom 36661 (antal dagar från 1 januari 1900), varför du måste formatera cellerna till ett datumformat.

Antag att du ska räkna förekomsten av värden utifrån ett givet datum. Även här i matrisformler fungerar DATUMVÄRDE-funktionen alldeles utmärkt:


I finansiella sammanhang brukar man vanligtvis räkna med 360 dagar. Antag att du vill erhålla antal dagar mellan två datum. M h a funktionerna DAGAR360 och DATUMVÄRDE erhålls snabbt svaret på frågan:


Vill man erhålla heltalet för ett viss datum kan det lösas på följande sätt:

 

Jag fick en förfrågan per e-post som knyter an till DATUMVÄRDE. Personen ville att XL skulle hålla reda på om dag 11 i en månad infaller på en lördag eller söndag. Om ja, så ska formeln visa datumet för den påföljande måndag istället.  Därutöver skulle man manuellt mata in månadsangivelse.

Lösningen blev relativ komplex men visar på styrkan med att ha kunskap om funktionen DATUMVÄRDE:

 

Formeln utgörs av en nästlad OM-funktion och där aktuell månad hämtas från cell A1.

Detta exempel finns ej att tillgå för download.

Bestämma betalningsdatum

En del användare nyttjar XL till fakturering. I det sammanhanget kan det vara en poäng att låta betalningsdatumet infalla under arbetsveckodagarna.

Antag att vi har 30 dagar netto som betalningsvillkor. Bilden nedan visar förutsättningarna i övrigt:



För att erhålla förfallodatumet kan följande formel användas:

Cellen som innehållet betalningsdatumet måste formateras mha ett datumformat.


Vill vi även erhålla vilken veckodag förfallodatumet infaller under kan det ske med följande formel: