Excel: Beregn uger - sådan fungerer det med en formel

Find den rigtige funktion

I Microsoft Excel er funktionen CALENDAR WEEK tilgængelig, som du kan vise kalenderugen for en bestemt dato med. For eksempel kan du automatisk vise i hvilken uge en aftale finder sted. CALENDAR WEEK -tabelfunktionen i Excel har også sine faldgruber. Det skyldes, at en kalenderuge er defineret anderledes i Tyskland, Østrig og Schweiz end i nabolande europæiske lande eller USA. Denne artikel viser, hvordan du stadig kan beregne den korrekte kalenderuge og forskellene mellem kalenderugen i DACH -regionen og andre lande.

CALENDAR WEEK -funktionen i Excel

CALENDAR WEEK -funktionen beregner også kalenderugerne i de europæiske versioner af Excel ved hjælp af den amerikanske standard. Den første dag på året er generelt tildelt kalenderuge 1.

Syntaksen til beregning af kalenderugen er:

= KALENDER UGE (DATO)

En kalenderuge defineres forskelligt i Tyskland, Østrig og Schweiz. Det er dybest set ugen, der som årets første uge består af mindst fire dage. Hvis den første januar falder på en fredag, lørdag eller søndag, returnerer tabelfunktionen CALENDAR WEEK et forkert resultat for Europa.

Bestem kalenderugerne korrekt i Excel - sådan fungerer det

For at vise den korrekte kalenderuge for Tyskland, Østrig eller Schweiz skal du bruge en formel. For eksempel skal dette være som følger for celle C4:

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

Formlen er kompleks og omfattende. For at forklare, hvordan det fungerer, overvejer vi de enkelte komponenter i formlen separat:

• Funktionen REST (A4-2; 7) vises flere gange i formlen. Denne funktion dividerer datoen med 7 og returnerer den resulterende rest. Denne funktion returnerer værdien 0 for mandage, værdien 1 for tirsdage op til en værdi af 6 for søndage.
• I formlen trækkes tallet 2 fra datoværdien. Grunden til dette er, at grunddatoen 1/1/1900 var en søndag. Delt med 7 resultater i en rest på 1. Af denne grund ville mandag blive løst til nummer 2. For at korrigere dagtællingen til 0 for søndag, 1 for mandag osv., Trækkes tallet 2 fra datoen.
• Baseret på denne hverdagsberegning giver delen (YEAR (A4-REST (A4-2; 7) +3) en årlig værdi reduceret med 1 for årets tre første dage, forudsat at disse dage er fredage, lørdage eller søndage handlinger.
• Denne årlige værdi er inkluderet i en dato inden for DATE -funktionen. Denne dato er generelt dannet med det andet argument 1 for januar måned.
• Som daglig værdi overføres REST -funktionen beskrevet ovenfor til DATE -funktionen minus værdien 9. Dette resulterer generelt i en negativ daglig værdi. Formlen viser, at datoen beregnet med DATE generelt er i december.
• For årets tre første dage er det dog december i året før næstsidste, forudsat at disse tre dage er fredage, lørdage eller søndage.
• Du fratrækker denne dato beregnet af DATE -funktionen fra datoen i kolonne A, dividerer forskellen med 7 (i de syv dage i ugen) Du bruger hele denne beregning inden for en KORT funktion for kun at få heltalsværdier.

Alternativt: Brug funktionsargumentet WOCHENSTART

En anden mulighed for at arbejde med funktionen CALENDAR WEEK i Microsoft Excel og opnå korrekte resultater er at tilføje funktionsargumentet week start til formlen. I lighed med formlen vist ovenfor kræver det en indsats at opbygge formlen.

Funktionsargumentet WOCHENSTART kan skelne mellem de to forskellige systemer til beregning af kalenderugen:

1. System 1 (Amerikansk system) - rapporterer uge 1 som den uge, der omfatter januar først.
2. System 2 (Europæisk standard i henhold til ISO 8601) - KW 1 er den uge, der inkluderer den første torsdag i året.

Hvis du bruger funktionsargumentet WOCHENSTART og kalenderuge -funktionen, skal du altid indtaste følgende tal som argumenter:

Mandag	2 eller 11
tirsdag	12
onsdag	13
torsdag	14
Fredag	15
lørdag	16
Søndag	1 eller 17

Forbind funktionen CALENDAR WEEK med funktionsargumentet WOCHENSTART i 6 trin

For at forbinde kalenderugefunktionen med funktionsargumentet uge start skal du gøre som følger:

1. Indtast en dato i dit Excel -regneark.

2. Venstreklik i cellen, hvor du vil inkorporere kalenderugen.

3. Skift til fanen FORMULER, og klik med venstre museknap på "Indsæt funktion".

   

4. Indtast CALENDAR WEEK i søgefeltet.

   

5. Når du har fundet funktionen CALENDAR WEEK, skal du bekræfte dit valg med OK.

6. Så snart du er i inputfeltet til funktionen, skal du indtaste formelkomponenterne som følger:

   • Serienummer = celle, der indeholder datoen, f.eks. A1.
   • Nummer type = numerisk værdi for ugedagen i henhold til tabellen ovenfor.
   

Kalenderugen udskrives korrekt i henhold til ISO 8601.

Sådan beregnes begyndelsen og slutningen på en kalenderuge i Excel

Kalenderuger spiller en afgørende rolle i professionel planlægning. Aftaler planlægges eller aftales ofte for en bestemt kalenderuge. I så fald skal du

1. Ved også hvilken kalenderuge en bestemt dato falder
2. Find ud af, hvilken dato en bestemt kalenderuge starter eller slutter.

Disse oplysninger hjælper dig f.eks., Når du planlægger produktionsdatoer i et industrielt miljø eller arrangerer leverings- eller faktureringsdatoer.

Ved hjælp af en særlig formel kan du nemt bestemme starten og slutningen på en kalenderuge. Formlen har følgende struktur:

= TEKST (DATO (årstal; 1; 7 * calweek-3-
WEEKDAY (DATE (årstal;;); 3)); "DD.MM.YY")
& "-" & TEKST (DATO (årstal; 1; 7 * kalenderuge + 3-
WEEKDAY (DATE (årstal;;); 3)); "DD.MM.YY")

Du sender to argumenter til formlen: Med argumentet Årstal definere det år, hvor kalenderugen er placeret. Du passerer kalenderugens nummer med argumentet Kalweek.

Resultatet af formlen er datoen for mandagen i denne kalenderuge efterfulgt af en bindestreg og datoen for søndagen i kalenderugen.

I den viste projektmappe leverer følgende formel i celle D4 perioden "09/30/13 - 10/6/13" som resultat:

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

Denne periode er kalenderuge 40 fra celle B4 i 2013 fra celle C4.

Resultatet af formlen udsender tekst, som du ikke kan bruge til at fortsætte beregningen. For at tydeliggøre den tidsperiode, der udsendes, kan du vise resultatet på en sådan måde, at ugens dage også udlæses. For at gøre dette skal du ændre nummerformatet i formelens to TEKST -funktioner som følger:

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

Efter kopiering af formlen til alle rækker i tabellen udskrives datoerne altid med ugens dage.

Sådan beregnes uger i Excel og finder den korrekte dato

I mange planer spiller uger en rolle som en enhed, f.eks. Kalenderuger eller produktionsuger. Uger kan også beregnes og vises i Excel ved hjælp af letforståelige formularer.

Følgende figur viser en liste over datoværdier i kolonne A. I kolonne B er der en værdi, der angiver, hvor mange uger denne datoværdi skal udskydes:

I kolonne C vil du bruge en formel, der beregner den nye dato baseret på datoen i kolonne A og antallet af uger i kolonne B. Hvis antallet af uger er en værdi større end nul, flyttes datoen til fremtiden, hvis værdien er negativ, flyttes den til fortiden.

Du kan nå dette mål ved at gange antallet af uger med værdien 7 og tilføje det til den oprindelige dato. Med tabelstrukturen beskrevet ovenfor ser formlen for celle C2 sådan ud:

= A1 + 7 * B1

Formlen er gældende, fordi Microsoft Excel gemmer datoværdier internt som tal. Afstanden fra en dag til en anden er 1. Den følgende figur viser brugen af formlen i en eksempel tabel:

Konklusion: Kalenderuger er afgørende for professionel aftalestyring

Aftaler bestemmer vores liv i både professionelle og private liv. Når man planlægger aftaler, bliver kalenderugerne vigtigere med stigende udnævnelsesintensitet, da de kan bruges til at skabe mere klarhed i aftaleplanlægningen.

Microsoft Excel er i stand til at angive den korrekte kalenderuge for hver dato. Den afgørende faktor her er, at brugere i Europa overholder det europæiske datosystem i henhold til ISO 8601, når de bygger deres Excel -formler. Som standard beregnes kalenderugen efter det amerikanske system, hvilket fører til afvigelser. I USA er den første dag på året generelt tildelt kalenderuge 1. I Tyskland, Østrig og Schweiz er den første kalenderuge derimod generelt defineret som den uge, der som årets første uge består af mindst fire dage. I praksis betyder det, at uge 1 i det europæiske system er den uge, der inkluderer årets første torsdag.

For den korrekte beregning af kalenderugen kan du bruge både funktionen CALENDAR WEEK og WEEKSTART -funktionsargumentet i Excel. Det er også muligt at oprette individuelle formler og tilpasse dem specifikt til dit regneark.