Excel: Beregn uker - slik fungerer det med en formel

Finn den riktige funksjonen

I Microsoft Excel er funksjonen CALENDAR WEEK tilgjengelig som du kan vise kalenderuken for en bestemt dato med. For eksempel kan du automatisk vise i hvilken uke en avtale finner sted. CALENDAR WEEK -tabellfunksjonen i Excel har også sine fallgruver. Dette er fordi en kalenderuke er definert annerledes i Tyskland, Østerrike og Sveits enn i nabolandene i Europa eller USA. Denne artikkelen viser hvordan du fremdeles kan beregne riktig kalenderuke og forskjellene mellom kalenderuken i DACH -regionen og andre land.

CALENDAR WEEK -funksjonen i Excel

CALENDAR WEEK -funksjonen beregner også kalenderuker i de europeiske versjonene av Excel ved hjelp av den amerikanske standarden. Den første dagen i året er vanligvis tilordnet kalenderuke 1.

Syntaksen for å beregne kalenderuken er:

= KALENDERUKE (DATO)

En kalenderuke er definert annerledes i Tyskland, Østerrike og Sveits. Det er i utgangspunktet uken som, som årets første uke, består av minst fire dager. Hvis den første januar faller på en fredag, lørdag eller søndag, returnerer tabellfunksjonen CALENDAR WEEK et feil resultat for Europa.

Bestem kalenderuker riktig i Excel - slik fungerer det

For å vise riktig kalenderuke for Tyskland, Østerrike eller Sveits, trenger du en formel. For eksempel må 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)

Formelen er kompleks og omfattende. For å forklare hvordan det fungerer, vurderer vi de enkelte komponentene i formelen separat:

• Funksjonen REST (A4-2; 7) vises flere ganger i formelen. Denne funksjonen deler datoen med 7 og returnerer den resulterende resten. Denne funksjonen returnerer verdien 0 for mandager, verdien 1 for tirsdager opp til verdien 6 for søndager.
• I formelen trekkes tallet 2 fra datoverdien. Grunnen til dette er at grunndatoen 1/1/1900 var en søndag. Delt med 7 resultater i resten av 1. Av denne grunn vil mandag bli løst til nummer 2. For å korrigere dagtellingen til 0 for søndag, 1 for mandag, etc., blir tallet 2 trukket fra datoen.
• Basert på denne ukedagsberegningen gir delen (YEAR (A4-REST (A4-2; 7) +3) en årlig verdi redusert med 1 for de tre første dagene i året, forutsatt at disse dagene er fredager, lørdager eller søndager handlinger.
• Denne årlige verdien er inkludert i en dato i DATE -funksjonen. Denne datoen dannes vanligvis med det andre argumentet 1 for januar måned.
• Som daglig verdi overfører du REST -funksjonen beskrevet ovenfor til DATE -funksjonen minus verdien 9. Dette resulterer vanligvis i en negativ daglig verdi. Formelen viser at datoen beregnet av DATE vanligvis er i desember.
• For de tre første dagene i året er det imidlertid desember i år før nest siste, forutsatt at disse tre dagene er fredager, lørdager eller søndager.
• Du trekker denne datoen som er beregnet med DATE -funksjonen fra datoen i kolonne A, deler differansen med 7 (for de 7 dagene i uken) Du bruker hele denne beregningen i en KORT -funksjon for å få bare heltallverdier.

Alternativt: Bruk funksjonsargumentet WOCHENSTART

En annen mulighet for å jobbe med CALENDAR WEEK -funksjonen i Microsoft Excel og for å oppnå riktige resultater er å legge til funksjonsargumentet week start til formelen. I likhet med formelen vist ovenfor, krever det innsats å bygge formelen.

Funksjonsargumentet WOCHENSTART kan skille mellom de to forskjellige systemene for å beregne kalenderuken:

1. System 1 (Amerikansk system) - rapporterer uke 1 som uken som inkluderer januar først.
2. System 2 (Europeisk standard i henhold til ISO 8601) - KW 1 er uken som inkluderer den første torsdagen i året.

Hvis du bruker funksjonsargumentet WOCHENSTART og kalenderukefunksjonen, må du alltid angi følgende tall som argumenter:

mandag	2 eller 11
tirsdag	12
onsdag	13
Torsdag	14
fredag	15
lørdag	16
søndag	1 eller 17

Koble funksjonen CALENDAR WEEK til funksjonsargumentet WOCHENSTART i 6 trinn

For å koble kalenderukefunksjonen til funksjonsargumentet uke start, gjør du som følger:

1. Skriv inn en dato i Excel -regnearket.

2. Venstreklikk i cellen du vil inkludere kalenderuken.

3. Bytt til kategorien FORMULER og klikk med venstre museknapp på "Sett inn funksjon".

   

4. Skriv inn CALENDAR WEEK i søkefeltet.

   

5. Etter at du har funnet funksjonen CALENDAR WEEK, bekrefter du valget med OK.

6. Så snart du er i inndatafeltet til funksjonen, skriver du inn formelkomponentene som følger:

   • Serienummer = celle som inneholder datoen, for eksempel A1.
   • Nummertype = numerisk verdi for ukedagen i henhold til tabellen ovenfor.
   

Kalenderuken er korrekt ut i henhold til ISO 8601.

Hvordan beregne begynnelsen og slutten av en kalenderuke i Excel

Kalenderuker spiller en avgjørende rolle i profesjonell planlegging. Avtaler er ofte planlagt eller avtalt for en bestemt kalenderuke. I så fall må du

1. Vet hvilken kalenderuke en bestemt dato også faller
2. Finn ut hvilken dato en bestemt kalenderuke starter eller slutter.

Denne informasjonen hjelper deg for eksempel når du planlegger produksjonsdatoer i et industrimiljø eller arrangerer leverings- eller faktureringsdatoer.

Ved hjelp av en spesiell formel kan du enkelt bestemme starten og slutten på en kalenderuke. Formelen har følgende struktur:

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

Du sender to argumenter til formelen: Med argumentet Årstall definere året som kalenderuken er plassert. Du passerer nummeret på kalenderuken med argumentet Kalweek.

Resultatet av formelen er datoen for mandagen i denne kalenderuken, etterfulgt av en bindestrek og datoen for søndagen i kalenderuken.

I arbeidsboken som vises, leverer følgende formel i celle D4 perioden "09/30/13 - 10/6/13" som resultat:

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

Denne perioden er kalenderuke 40 fra celle B4 i 2013 fra celle C4.

Resultatet av formelen sender ut tekst som du ikke kan bruke til å fortsette beregningen. For å tydeliggjøre tidsperioden som sendes ut, kan du vise resultatet på en slik måte at ukedagene også sendes ut. For å gjøre dette, endre tallformatet i de to TEKST -funksjonene i formelen som følger:

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

Etter at formelen er kopiert til alle radene i tabellen, blir datoene alltid skrevet ut med ukedagene.

Hvordan beregne uker i Excel og finne riktig dato

I mange planer spiller uker en rolle som en enhet, for eksempel kalenderuker eller produksjonsuker. Uker kan også beregnes og vises i Excel ved hjelp av lettfattelige skjemaer.

Følgende figur viser en liste over datoverdier i kolonne A. I kolonne B er det en verdi som angir hvor mange uker denne datoverdien skal utsettes:

I kolonne C vil du bruke en formel som beregner den nye datoen basert på datoen i kolonne A og antall uker i kolonne B. Hvis antall uker er en verdi større enn null, flyttes datoen til fremtiden, hvis verdien er negativ, flyttes den til fortiden.

Du kan nå dette målet ved å multiplisere antall uker med verdien 7 og legge den til den opprinnelige datoen. Med tabellstrukturen beskrevet ovenfor ser formelen for celle C2 slik ut:

= A1 + 7 * B1

Formelen gjelder fordi Microsoft Excel lagrer datoverdier internt som tall. Avstanden fra en dag til en annen er 1. Figuren nedenfor viser bruken av formelen i en eksempeltabell:

Konklusjon: Kalenderuker er avgjørende for profesjonell avtalehåndtering

Avtaler avgjør livet vårt i både profesjonelle og private liv. Når du planlegger avtaler, blir kalenderukene viktigere med økende avtaleintensitet, ettersom de kan brukes til å skape mer klarhet i avtaleplanleggingen.

Microsoft Excel kan angi riktig kalenderuke for hver dato. Den avgjørende faktoren her er at brukere i Europa observerer det europeiske datasystemet i henhold til ISO 8601 når de bygger sine Excel -formler. Som standard er kalenderuken beregnet i henhold til det amerikanske systemet, noe som fører til avvik. I USA er den første dagen i året vanligvis tilordnet kalenderuke 1. I Tyskland, Østerrike og Sveits, derimot, er den første kalenderuken generelt definert som uken som, som den første uken i året, består av minst fire dager. I praksis betyr dette at uke 1 i det europeiske systemet er uken som inkluderer den første torsdagen i året.

For riktig beregning av kalenderuken kan du bruke både CALENDAR WEEK -funksjonen og WEEKSTART -funksjonsargumentet i Excel. Det er også mulig å sette opp individuelle formler og tilpasse dem spesielt til regnearket.