Hitta alla datumen!
När man skapar datumtabeller kan det underlätta att låta XL generera datumserierna.

Här demonstreras en teknik med vilken man kan skapa skilda datumserier.

Antag att vi behöver få fram datum för alla onsdagar under en viss period och utifrån givna datum.

Tabellen nedan visar utfallet:

Den underliggande formeln för att erhålla rätt datum är följande:

• =(HELTAL(A1/7)*7)+4

Det som styr vilket datum som ska fås fram är det sista värdet i formeln, värdet 4 står följaktligen för onsdagar. För att få fram datumformatet krävs att cellerna formateras till datumformat.

Projektslutdatum
Antag att du har ett projekt eller en aktivitet som har sitt bestämda startdatum samt antal arbetsdagar det får åtgå till det.

Frågan som uppstår är vid vilket datum ska projektet/aktiviteten nå sitt slut?

Bilden nedan visar ett exempel på detta scenario och där XL även kan lösa problemet:

Lösningen består i att använda sig av funktionen ARBETSDAGAR (Ingår i tilläggsverktyget Analysis Toolpack som medföljer XL).

• =ARBETSDAGAR(A2;B2)

Nedräkning!
Har du funderat på hur man kan skapa en nedräkningsfunktion i XL? Här presenteras en formelteknik som håller reda på antal år, månader och dagar kvar till ett visst datum.

Bilden nedan visar exemplet ifråga:

För att erhålla antal år används följande delformel:

• =OM(DATEDIF(IDAG();B1;"y")<>1;DATEDIF(IDAG();B1;"y")&
  " År ";DATEDIF(IDAG();B1;"y")&" År ")

För att erhålla antal månad(er) används följande delformel:

• =OM(DATEDIF(IDAG();B1;"ym")<>1;DATEDIF(IDAG();B1;"ym")&
  " Månader ";DATEDIF(IDAG();B1;"ym")&" Månad ")

För att erhåll antal dag(ar) används följande delformel:

• =OM(DATEDIF(IDAG();B1;"md")<>1;DATEDIF(IDAG();B1;"md")&
  " Dagar kvar.";DATEDIF(IDAG();B1;"md")&" dag kvar.")

Slutligen för att redovisa allt i en cell sätts dessa formler ihop till en enda...:

• =OM(DATEDIF(IDAG();B1;"y")<>1;DATEDIF(IDAG();B1;"y")&" År ";
  DATEDIF(IDAG();B1;"y")&" År") &OM(DATEDIF(IDAG();B1;"ym")<>
  1;DATEDIF(IDAG();B1;"ym")&" Månader ";DATEDIF(IDAG();B1;"ym")&" Månad")&OM(DATEDIF(IDAG();B1;"md")<>1;DATEDIF
  (IDAG();B1;"md")&" Dagar kvar.";DATEDIF(IDAG();B1;"md")&" dag kvar.")

Erhålla datum från år, vecko- och veckodagnummer
I vissa sammanhang kan det vara önskvärt att identifiera datum utifrån årtal, veckonummer och veckodagnummer.

Då XL beräknar veckonummer utifrån amerikanska förhållandena måste vi väga in det vid beräkning av datumet.

Följande exempel föreligger:

För att erhålla datumet används följande formel:

• =((B1&"/1")-REST(B1&"/1";7)-REST((B1&"/1")-REST(B1&"/1";7)
  +3;7)-2+B2*7)+B3-1

Första & Sista datumet för veckodag
Här demonstreras tekniker för att snabbt få fram första och sista datumet för t ex måndag i en specifik månad.

Exemplet visas i nedanstående bild i sin helhet:

För att erhålla första måndagens datum:

• =DATUM(ÅR(A1);MÅNAD(A1);1)+REST(8-VECKODAG(DATUM
  (ÅR(A1);MÅNAD(A1);1));6)

Vill vi erhålla datumet för en annan veckodag får vi ändra på det sista värdet i formeln. För tisdag anges värdet 5 och för söndag värdet 7.

För att erhålla sista måndagens datum:

• =DATUM(ÅR(A1);MÅNAD(A1)+1;1)-VECKODAG
  (DATUM(ÅR(A1);MÅNAD(A1)+1;1)-2)

För att få fram datumet för en annan veckodag måste det sista värdet i formeln ändras. För tisdag anges värdet -3 och för söndag värdet -1.

Hitta de sista x veckodagarna
I detta tips demonstreras hur vi kan t ex erhålla de fyra sista onsdagarna i en period.

Detta kan komma väl tillhands om vi t ex håller på och summerar löpande data som är kopplad till tids- och datumuttryck.

Exemplet bygger på följande tabell:

Lösningen ligger i cellen D3, som innehåller följande formel:

• =IDAG()-VECKODAG(IDAG();2)-4

Formeln ger det önskade resultatet genom att vi reducerar uttrycket med 4 då returtypen i VECKODAG ger söndagar = 7 och vill vi ha onsdagar (7-4 = 3).

Följande enklare formler ligger grund för de övriga datumen:

• D2: =D3+7
   
• D4: =D3-7
   
• D5: =D4-7

För att hämta värden till dessa datum används följande formel:

• =LETARAD(D2;$A$2:$B$46;2;0)

Gäller garantin?
I detta tips visas hur vi beräknar om en order fortfarande har garantin kvar eller inte.

I exemplet används en garantitid om 30 månader. Antal månader mellan inköpsdatum och dagens datum avgör om garantin fortfarande löper eller inte.

Bilden nedan visar förutsättningarna för exemplet:

Bokstäverna i variabeln "Serienr" representerar årtal och månad, där första bokstaven representerar årtalet.

Det finns följande tabell ur vilken vi kan utläsa vad bokstavsbeteckningen för respektive serienummer representerar:

Exempelvis betyder följande serienummer:

• 4520-EF: 1996-06
   
• 2022-JC: 2001-03

Följande formel ger svar på huruvida garantin löper eller inte:

• =OM(DATEDIF(1*(LETARAD(EXTEXT(C3;LÄNGD(C3)-1;1);Tabell;2;0)&
  "-"&LETARAD(HÖGER(C3;1);Tabell;3;0)&"-01");1*(ÅR(NU())&"-"&
  MÅNAD(NU())&"-01");"M")>$B$1;"Nej";"Ja")

Namnet Tabell refererar till cellområdet F3:H14

Vad formeln gör är följande:

• Extraherar bokstaven för årtal och letar reda på motsvarande årtal i tabellen. Samma sak sker för månad.
   
• Lägger till dag 1 och multipliceras med 1 för att få ett numeriskt och därmed beräkningsbart inköpsdatumvärde. Samma sak sker för dagens datumvärde.
   
• DATEDIF-funktionen beräknar skillnaden i månader mellan inköpsdatum och dagens datum.
   
• OM-funktionens villkor, differensen mellan inköpsdatum och dagens datum är större än den angivna garantitiden, styr vilket svar som erhålls.

Räkna alla veckodagarna
Här demonstreras två tekniker för att erhålla antalet dagar för respektive veckodag i en datumlista.

Exemplet har sin utgångspunkt i följande uppställning:

I den första lösningen används följande matrisformel:

• {=SUMMA((VECKODAG($A$2:$A$18;2)=B2)*1)}

I den andra lösningen används formeln PRODUKTSUMMA

• =PRODUKTSUMMA((VECKODAG($A$2:$A$18;2)=B2)*1)

Vardagar
I detta tips demonstreras hur vi kan identifiera den första vardagen efter eller den sista vardagen före ett givet datum.

Exemplet i sin helhet:

För att erhålla första vardagens datum används följande formel:

• =EDATUM(A2;0)+1+VÄLJ(REST(EDATUM(A2;0);7)+1;1;0;0;0;0;0;2)

För att erhålla sista vardagens datum används formel:

• =EDATUM(A2;0)-VÄLJ(REST(EDATUM(A2;0);7)+1;1;2;0;0;0;0;0)

För att få tillgång till funktionen EDATUM måste tilläggsverktyget Analysis Toolpak vara installerat.

Datumintervallberäkningar
Här visas ett flertal formeltekniska lösningar för beräkningar mellan ett start- och slutdatum.

Exemplet har sin utgångspunkt i följande uppställning:

För att få fram summan i datumintervallet används följande formel:

• =PRODUKTSUMMA(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2)*$B$2:$B$7)

För att få fram medelvärdet i datumintervallet används följande formel:

• =PRODUKTSUMMA(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2)*$B$2:$B$7)/(DATEDIF($D2;$E2;"d")+1)

För att få fram största respektive minsta värdet inom intervallet används följande två matrisformler:

• {=MAX(OM(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2);$B$2:$B$7;""))}
   
• {=MIN(OM(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2);$B$2:$B$7;""))}

Vill vi erhålla det näst största respektive minsta värdet i datumintervallet kan vi använda oss av följande matrisformler:

• {=STÖRSTA(OM(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2);$B$2:$B$7;"");2)}
   
• {=MINSTA(OM(($A$2:$A$7>=$D2)*($A$2:$A$7
  <=$E2);$B$2:$B$7;"");2)}

Veckonummer i månad
I detta tips visar hur vi kan få fram vilket veckonummer i en månad som ett visst datum representerar.

Bilden nedan visar exemplet i sin helhet:

Om vi arbetar med veckodagar där vi utgår från att veckan börjar på söndagar (amerikansk modell) används följande formel:

• =HELTAL((DAG(A1)-1)/7+1)+ (VECKODAG(A1)
  <VECKODAG(DATUM(ÅR(A1);MÅNAD(A1);1)))

Använder vi oss av svensk vecka, dvs veckan börjar på måndagar, används följande formel:

• =HELTAL((DAG(A1)-1)/7+1)+ (VECKODAG(A1;2)
  <VECKODAG(DATUM(ÅR(A2);MÅNAD(A2);1);2))

Dagens datum eller det närmaste
Antag att vi har en lista med bl a datum och där vi vill få reda på om dagens datum finns eller om det inte finns i listan det närmaste datumet.

Exemplet visas i sin helhet i följande bild:

Problemet kan visserligen lösas på ett flertal sätt men följande formel gör det på ett enkelt sätt:

• =MIN(IDAG();MAX(A1:A8))

Formeln kan även användas för att få fram huruvida ett värde finns eller ej och om det inte finns få fram det högsta närmaste värdet.

Konvertera ofullständiga datum till korrekta
Vid import av textfiler kan datumangivelser många gånger vålla problem. Ska vi dessutom utföra beräkningar med datumen blir det ytterligare en försvårande omständighet. Detta tips visar på ett sätt att lös problemet ifråga.

Följande förutsättningar gäller i exemplet:

• Datum med 6 siffror angivna tillhör 90-talet.
   
• Datum med 3 eller 4 siffror tillhör år 2000.
   
• Datum med 5 siffror tillhör år 200x.

Följande bild visar exemplet i sin helhet:

Formeln för att välja rätt formatering och omvandla text till datum ser ut på följande sätt:

• =OM(LÄNGD(A2)=6;19&VÄNSTER(A2;2)&"-"&EXTEXT
  (A2;3;2)&"-"&HÖGER(A2;2);OM(LÄNGD(A2)=3;2000&"-"&
  "0"&VÄNSTER(A2;1)&"-"&HÖGER(A2;2);OM(LÄNGD(A2)=
  4;2000&"-"&VÄNSTER(A2;2)&"-"&HÖGER(A2;2);OM
  (LÄNGD(A2)=5;200&VÄNSTER(A2;1)&"-"&
  EXTEXT(A2;2;2)&"-"&HÖGER(A2;2);""))))*1

Cellområdet måste formateras till ett datumformat.

Produktsumma med datumvillkor
I detta tips demonstreras hur vi kan utföra beräkningar där vi använder oss av datumvillkor.

Bilden nedan visar exemplet i sin helhet:

I det första exemplet ska posterna uppfylla villkoret År=2003 och Månad=2 där följande formel ger oss ett önskat resultat i cell E3:

• =PRODUKTSUMMA((ÅR(A2:A11)=E1)*(MÅNAD(A2:A11)
  =E2);B2:B11)

I det andra exemplet ska posterna falla inom intervallet 2003-02 - 2004-02 vars resultat erhålls med följande formel:

• =PRODUKTSUMMA((ÅR(A2:A11)>=E1)*(MÅNAD(A2:A11)>
  =E2)*B2:B11)-PRODUKTSUMMA((ÅR(A2:A11)>=E5)
  *(MÅNAD(A2:A11)>E6)*B2:B11)

Förekommer det tomma poster måste det beaktas såsom i följande exempel:

• =PRODUKTSUMMA((ÅR(A2:A11)=E1)*(MÅNAD(A2:A11)
  =E2)*(A2:A11<>"")*B2:B11)

Minsta o största månadsbeloppet
I exemplet antas att vi vill erhålla mini- respektive maxvärdet för en månad oavsett år. De ösnakde beloppen ska dessutom överstiga 100, dvs minivärdet måste vara större än 100 och maxvärdet måste vara större än 100.

Bilden nedan visar exemplet i sin helhet:

För att erhålla minivärdet används följande matrisformel:

• {=MIN(OM((MÅNAD(A2:A9)=E1)*(B2:B9>100);B2:B9))}

För att erhålla maxivärdet används följande formel:

• {=MAX(OM((MÅNAD(A2:A9)=E4)*(B2:B9>100);B2:B9))}

Upp