Excel: Beräkna veckor - så här fungerar det med en formel

Hitta rätt funktion

I Microsoft Excel är funktionen CALENDAR WEEK tillgänglig med vilken du kan visa kalenderveckan för ett visst datum. Till exempel kan du automatiskt visa vilken vecka ett möte äger rum. CALENDAR WEEK -tabellfunktionen i Excel har också sina fallgropar. Detta beror på att en kalendervecka definieras annorlunda i Tyskland, Österrike och Schweiz än i grannländerna i Europa eller USA. Den här artikeln visar hur du fortfarande kan beräkna rätt kalendervecka och skillnaderna mellan kalenderveckan i DACH -regionen och andra länder.

CALENDAR WEEK -funktionen i Excel

CALENDAR WEEK -funktionen beräknar också kalenderveckorna i de europeiska versionerna av Excel med amerikansk standard. Den första dagen på året tilldelas i allmänhet kalendervecka 1.

Syntaxen för att beräkna kalenderveckan är:

= KALENDERVECKA (DATUM)

En kalendervecka definieras annorlunda i Tyskland, Österrike och Schweiz. Det är i princip veckan som, som årets första vecka, består av minst fyra dagar. Om den första januari infaller på en fredag, lördag eller söndag returnerar tabellfunktionen CALENDAR WEEK ett felaktigt resultat för Europa.

Bestäm kalenderveckorna korrekt i Excel - så här fungerar det

För att visa rätt kalendervecka för Tyskland, Österrike eller Schweiz behöver du en formel. Till exempel måste detta vara följande för cell C4:

= TRIM ((A4-DATE (YEAR (A4-REMAIN (A4-2,7) +3); 1; REMAIN (A4-2,7) -9)) / 7)

Formeln är komplex och omfattande. För att förklara hur det fungerar betraktar vi de enskilda komponenterna i formeln separat:

• Funktionen REST (A4-2; 7) visas flera gånger i formeln. Denna funktion dividerar datumet med 7 och returnerar den resulterande återstoden. Denna funktion returnerar värdet 0 för måndagar, värdet 1 för tisdagar upp till värdet 6 för söndagar.
• I formeln subtraheras siffran 2 från datumvärdet. Anledningen till detta är att basdatumet 1/1/1900 var en söndag. Delat med 7 resultat i återstoden av 1. Av denna anledning skulle måndagen löses till nummer 2. För att korrigera dagräkningen till 0 för söndag, 1 för måndag, etc., subtraheras siffran 2 från datumet.
• Baserat på denna vardagskalkyl ger delen (YEAR (A4-REST (A4-2; 7) +3) ett årligt värde reducerat med 1 för årets tre första dagar, förutsatt att dessa dagar är fredagar, lördagar eller söndagar handlingar.
• Detta årliga värde ingår i ett datum inom DATE -funktionen. Detta datum bildas i allmänhet med det andra argumentet 1 för januari månad.
• Som det dagliga värdet överför du REST -funktionen som beskrivs ovan till DATE -funktionen minus värdet 9. Detta resulterar i allmänhet i ett negativt dagligt värde. Formeln visar att datumet beräknat av DATE i allmänhet är i december.
• Under årets tre första dagar är det dock december på året före näst sista, förutsatt att dessa tre dagar är fredagar, lördagar eller söndagar.
• Du subtraherar detta datum beräknat med DATE -funktionen från datumet i kolumn A, dividerar skillnaden med 7 (för de sju dagarna i veckan) Du använder hela denna beräkning inom en KORT -funktion för att endast få heltal.

Alternativt: Använd funktionsargumentet WOCHENSTART

En annan möjlighet att arbeta med CALENDAR WEEK -funktionen i Microsoft Excel och för att uppnå korrekta resultat är att lägga till funktionsargumentet vecka start till formeln. I likhet med formeln som visas ovan tar det ansträngning att bygga formeln.

Funktionsargumentet WOCHENSTART kan skilja mellan de två olika systemen för att beräkna kalenderveckan:

1. System 1 (Amerikanskt system) - rapporterar vecka 1 som veckan som inkluderar januari först.
2. System 2 (Europeisk standard enligt ISO 8601) - KW 1 är veckan som inkluderar årets första torsdag.

Om du använder funktionsargumentet WOCHENSTART och kalenderveckofunktionen måste du alltid ange följande nummer som argument:

Måndag	2 eller 11
Tisdag	12
Onsdag	13
Torsdag	14
fredag	15
Lördag	16
Söndag	1 eller 17

Länka funktionen CALENDAR WEEK till funktionsargumentet WOCHENSTART i 6 steg

Gör så här för att länka kalenderveckofunktionen till funktionsargumentet veckostart:

1. Ange ett datum i ditt Excel -kalkylblad.

2. Vänsterklicka i cellen där du vill inkludera kalenderveckan.

3. Byt till fliken FORMULER och klicka med vänster musknapp på "Infoga funktion".

   

4. Ange KALENDERVECKA i sökfältet.

   

5. När du har hittat funktionen CALENDAR WEEK bekräftar du ditt val med OK.

6. Så snart du är i inmatningsfältet för funktionen anger du formelkomponenterna enligt följande:

   • Serial_number = cell som innehåller datumet, till exempel A1.
   • Numrertyp = numeriskt värde för veckodagen enligt tabellen ovan.
   

Kalenderveckan matas ut korrekt enligt ISO 8601.

Hur man beräknar början och slutet av en kalendervecka i Excel

Kalenderveckor spelar en avgörande roll för professionell schemaläggning. Möten planeras eller avtalas ofta för en viss kalendervecka. I så fall måste du

1. Vet vilken kalendervecka ett visst datum också faller
2. Ta reda på vilket datum en specifik kalendervecka börjar eller slutar.

Denna information hjälper dig till exempel när du planerar produktionsdatum i en industriell miljö eller ordnar leverans- eller faktureringsdatum.

Med en speciell formel kan du enkelt bestämma början och slutet av en kalendervecka. Formeln har följande struktur:

= TEXT (DATUM (årtal; 1; 7 * calweek-3-
WEEKDAY (DATE (årtal;;); 3)); "DD.MM.YY")
& "-" & TEXT (DATE (årnummer; 1; 7 * kalendervecka + 3-
WEEKDAY (DATE (årtal;;); 3)); "DD.MM.YY")

Du skickar två argument till formeln: Med argumentet Årstal definiera året då kalenderveckan är belägen. Du passerar kalenderveckans nummer med argumentet Kalweek.

Resultatet av formeln är datumet för måndagen i denna kalendervecka, följt av en bindestreck och datumet för söndagen i kalenderveckan.

I arbetsboken som visas levererar följande formel i cell D4 perioden "09/30/13 - 10/6/13" som resultat:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATE (C4;;); 3)); "DD.MM.YY") & "-" & TEXT
(DATE (C4; 1; 7 * B4 + 3-WEEKDAY (DATE (C4 ;;); 3));
"DD.MM.ÅÅ")

Denna period är kalendervecka 40 från cell B4 2013 från cell C4.

Resultatet av formeln matar ut text som du inte kan använda för att fortsätta beräkna. För att klargöra den tidsperiod som matas ut kan du visa resultatet på ett sådant sätt att veckodagarna också matas ut. För att göra detta, ändra nummerformatet i de två TEXT -funktionerna i formeln enligt följande:

= TEXT (DATE (C4; 1; 7 * B4-3-WEEKDAY
(DATUM (C4;;); 3)); "DDD DD.MM.
ÅÅ ") &" - "& TEXT (DATUM (C4; 1; 7 * B4 + 3-
WEEKDAY (DATE (C4;;); 3)); "DDDDD.MM.YY")

Efter att formeln har kopierats till alla rader i tabellen matas datumen alltid ut med veckodagarna.

Hur man beräknar veckor i Excel och hittar rätt datum

I många planer spelar veckor en roll som enhet, till exempel kalenderveckor eller produktionsveckor. Veckor kan också beräknas och visas i Excel med hjälp av lättförståliga formulär.

Följande bild visar en lista över datumvärden i kolumn A. I kolumn B finns ett värde som anger hur många veckor detta datumvärde ska skjutas upp:

I kolumn C vill du använda en formel som beräknar det nya datumet baserat på datumet i kolumn A och antalet veckor i kolumn B. Om antalet veckor är ett värde större än noll, flyttas datumet till framtiden, om värdet är negativt flyttas det till det förflutna.

Du kan uppnå detta mål genom att multiplicera antalet veckor med värdet 7 och lägga till det i det ursprungliga datumet. Med tabellstrukturen som beskrivs ovan ser formeln för cell C2 ut så här:

= A1 + 7 * B1

Formeln är tillämplig eftersom Microsoft Excel lagrar datumvärden internt som siffror. Avståndet från en dag till en annan är 1. Följande bild visar användningen av formeln i en exempeltabell:

Slutsats: Kalenderveckor är avgörande för professionell möteshantering

Möten avgör vårt liv i både yrkeslivet och privatlivet. När du planerar möten blir kalenderveckorna viktigare med ökande utnämningsintensitet, eftersom de kan användas för att skapa mer tydlighet i mötesplaneringen.

Microsoft Excel kan ange rätt kalendervecka för varje datum. Den avgörande faktorn här är att användare i Europa följer det europeiska datumsystemet enligt ISO 8601 när de bygger sina Excel -formler. Som standard beräknas kalenderveckan enligt det amerikanska systemet, vilket leder till avvikelser. I USA är den första dagen på året i allmänhet tilldelad kalendervecka 1. I Tyskland, Österrike och Schweiz, däremot, definieras den första kalenderveckan generellt som den vecka som, som årets första vecka, består av minst fyra dagar. I praktiken innebär detta att vecka 1 i det europeiska systemet är veckan som inkluderar årets första torsdag.

För korrekt beräkning av kalenderveckan kan du använda både funktionen KALENDERVECKA och WEEKSTART -funktionsargumentet i Excel. Det är också möjligt att ställa in individuella formler och anpassa dem specifikt till ditt kalkylblad.