Excel: Izračunajte tjedne - ovako to funkcionira s formulom

Pronađite pravu funkciju

U programu Microsoft Excel dostupna je funkcija TJEDAN KALENDARA s kojom možete prikazati kalendarski tjedan za određeni datum. Na primjer, možete automatski prikazati u kojem tjednu se sastanak održava. Funkcija tablice KALENDARSKI TJEDAN u Excelu također ima svoje zamke. To je zato što je kalendarski tjedan drugačije definiran u Njemačkoj, Austriji i Švicarskoj nego u susjednim europskim zemljama ili SAD -u. Ovaj članak prikazuje kako još uvijek možete izračunati ispravan kalendarski tjedan i razlike između kalendarskog tjedna u DACH regiji i drugim zemljama.

Funkcija TJEDAN KALENDARA u Excelu

Funkcija KALENDARSKI TJEDAN također izračunava kalendarske tjedne u europskim verzijama programa Excel koristeći američki standard. Prvi dan u godini općenito se dodjeljuje kalendarskom tjednu 1.

Sintaksa za izračunavanje kalendarskog tjedna je:

= KALENDARSKI TJEDAN (DATUM)

Kalendarski tjedan različito je definiran u Njemačkoj, Austriji i Švicarskoj. To je u osnovi tjedan koji se, kao i prvi tjedan u godini, sastoji od najmanje četiri dana. Ako prvi siječanj pada u petak, subotu ili nedjelju, funkcija tablice CALENDAR WEEK vraća netočan rezultat za Europu.

Ispravno odredite kalendarske tjedne u Excelu - ovako to funkcionira

Za prikaz ispravnog kalendarskog tjedna za Njemačku, Austriju ili Švicarsku potrebna vam je formula. Na primjer, ovo mora biti sljedeće za ćeliju C4:

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

Formula je složena i opsežna. Kako bismo objasnili kako to funkcionira, pojedinačne komponente formule razmatramo zasebno:

• Funkcija REST (A4-2; 7) pojavljuje se nekoliko puta u formuli. Ova funkcija dijeli datum sa 7 i vraća rezultirajući ostatak.Ova funkcija vraća vrijednost 0 za ponedjeljak, vrijednost 1 za utorak do vrijednosti 6 za nedjelju.
• U formuli se broj 2 oduzima od vrijednosti datuma. Razlog tome je što je bazni datum 1.1.1900 bila nedjelja. Podijeljeno sa 7 rezultira ostatkom 1. Iz tog razloga, ponedjeljak će biti riješen na broj 2. Da biste ispravili brojanje dana na 0 za nedjelju, 1 za ponedjeljak itd., Broj 2 se oduzima od datuma.
• Na temelju izračuna ovog radnog dana, dio (GODINA (A4-ODMOR (A4-2; 7) +3) daje godišnju vrijednost umanjenu za 1 za prva tri dana u godini, pod uvjetom da su ti dani petkom, subotom ili nedjeljom djela.
• Ova godišnja vrijednost uključena je u datum unutar funkcije DATE. Taj se datum općenito formira s drugim argumentom 1 za mjesec siječanj.
• Kao dnevnu vrijednost, prenesite gore opisanu funkciju REST na funkciju DATE minus vrijednost 9. To općenito rezultira negativnom dnevnom vrijednošću. Formula pokazuje da je datum izračunat prema DATE općenito u prosincu.
• Za prva tri dana u godini, međutim, prosinac je godine prije pretposljednje godine, pod uvjetom da su ta tri dana petkom, subotom ili nedjeljom.
• Ovaj datum izračunat funkcijom DATE oduzimate od datuma u stupcu A, razliku dijelite sa 7 (za 7 dana u tjednu). Cijeli ovaj izračun koristite unutar funkcije KRATKO kako biste dobili samo cijele vrijednosti.

Alternativno: Upotrijebite argument funkcije WOCHENSTART

Druga mogućnost rada s funkcijom TJEDAN KALENDARA u Microsoft Excelu i postizanje točnih rezultata je dodavanje formule tjedni argument argument početak tjedna. Slično formuli prikazanoj gore, potreban je napor da se izgradi formula.

Argument funkcije WOCHENSTART može razlikovati dva različita sustava za izračunavanje kalendarskog tjedna:

1. Sustav 1 (Američki sustav) - izvještava 1. tjedan kao tjedan koji uključuje prvi siječanj.
2. Sustav 2 (Europski standard prema ISO 8601) - KW 1 je tjedan koji uključuje prvi četvrtak u godini.

Ako koristite argument funkcije WOCHENSTART i funkciju kalendarskog tjedna, uvijek morate unijeti sljedeće brojeve kao argumente:

ponedjeljak	2 ili 11
utorak	12
srijeda	13
četvrtak	14
petak	15
subota	16
nedjelja	1 ili 17

Povežite funkciju TJEDAN KALENDARA s argumentom funkcije WOCHENSTART u 6 koraka

Za povezivanje funkcije kalendarski tjedan s argumentom funkcije početak tjedna, postupite na sljedeći način:

1. Unesite datum u Excel proračunsku tablicu.

2. Lijevom tipkom miša kliknite ćeliju u koju želite uvrstiti kalendarski tjedan.

3. Prijeđite na karticu FORMULAS i kliknite lijevom tipkom miša na "Insert function".

   

4. U polje za pretraživanje unesite TJEDAN KALENDARA.

   

5. Nakon što pronađete funkciju TJEDAN KALENDARA, potvrdite svoj odabir s U redu.

6. Čim se nađete u polju za unos funkcije, unesite komponente formule na sljedeći način:

   • Serijski_broj = ćelija koja sadrži datum, na primjer A1.
   • Vrsta broja = brojčana vrijednost za dan u tjednu prema gornjoj tablici.
   

Kalendarski tjedan ispravno je ispisan prema ISO 8601.

Kako izračunati početak i kraj bilo kojeg kalendarskog tjedna u Excelu

Kalendarski tjedni imaju ključnu ulogu u profesionalnom rasporedu. Termini se često planiraju ili dogovaraju za određeni kalendarski tjedan. U tom slučaju morate

1. Znajte i koji kalendarski tjedan pada određeni datum
2. Saznajte koji datum počinje ili završava određeni kalendarski tjedan.

Ove vam informacije pomažu, na primjer, pri planiranju datuma proizvodnje u industrijskom okruženju ili dogovaranju datuma isporuke ili naplate.

Pomoću posebne formule možete jednostavno odrediti početak i kraj bilo kojeg kalendarskog tjedna. Formula ima sljedeću strukturu:

= TEKST (DATUM (broj godine; 1; 7 * kalweek-3-
TJEDAN (DATE (broj godine ;;); 3)); "DD.MM.GGG")
& "-" & TEXT (DATE (broj godine; 1; 7 * kalendarski tjedan + 3-
TJEDAN (DATE (broj godine ;;); 3)); "DD.MM.GGG")

Formuli prenosite dva argumenta: S argumentom Broj godine definirati godinu u kojoj se nalazi kalendarski tjedan. Prosljeđujete broj kalendarskog tjedna s argumentom Kalweek.

Rezultat formule je datum ponedjeljka ovog kalendarskog tjedna, nakon čega slijedi crtica i datum nedjelje kalendarskog tjedna.

U prikazanoj radnoj knjizi sljedeća formula u ćeliji D4 daje razdoblje "09/30/13 - 10/6/13" kao rezultat:

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

Ovo je razdoblje 40. kalendarski tjedan iz ćelije B4 2013. iz ćelije C4.

Rezultat formule daje tekst koji ne možete koristiti za nastavak izračuna. Kako biste pojasnili izlazni vremenski period, možete prikazati rezultat na takav način da se ispisuju i dani u tjednu. Da biste to učinili, promijenite format broja u dvije funkcije TEXT formule na sljedeći način:

= TEKST (DATUM (C4; 1; 7 * B4-3-TJEDAN
(DATUM (C4 ;;); 3)); „DDD DD.MM.
YY “) &“ - “& TEXT (DATUM (C4; 1; 7 * B4 + 3-
TJEDAN (DATUM (C4 ;;); 3)); "DDDDD.MM.GGG")

Nakon kopiranja formule u sve retke tablice, datumi se uvijek ispisuju s danima u tjednu.

Kako izračunati tjedne u Excelu i pronaći točan datum

U mnogim planovima tjedni imaju ulogu jedinice, na primjer kalendarski tjedni ili tjedni proizvodnje. Tjedno se mogu izračunati i prikazati u Excelu pomoću lako razumljivih obrazaca.

Sljedeća slika prikazuje popis vrijednosti datuma u stupcu A. U stupcu B postoji vrijednost koja označava koliko tjedana ovu vrijednost datuma treba odgoditi:

U stupcu C želite koristiti formulu koja izračunava novi datum na temelju datuma u stupcu A i broja tjedana u stupcu B. Ako je broj tjedana vrijednost veća od nule, datum se pomiče u budućnost, ako je vrijednost negativna, pomiče se u prošlost.

Ovaj cilj možete postići tako da broj tjedana pomnožite s vrijednošću 7 i dodate ga izvornom datumu. S gore opisanom strukturom tablice, formula za ćeliju C2 izgleda ovako:

= A1 + 7 * B1

Formula je primjenjiva jer Microsoft Excel interno pohranjuje vrijednosti datuma kao brojeve. Udaljenost od jednog dana do drugog iznosi 1. Sljedeća slika prikazuje uporabu formule u tablici primjera:

Zaključak: Kalendarski tjedni ključni su za profesionalno vođenje termina

Imenovanja određuju naš život u profesionalnom i privatnom životu. Prilikom planiranja obveza kalendarski tjedni postaju sve važniji s povećanjem intenziteta termina jer se mogu koristiti za stvaranje veće jasnoće u planiranju obveza.

Microsoft Excel može odrediti ispravan kalendarski tjedan za svaki datum. Ovdje je odlučujući faktor to što korisnici u Europi pri izgradnji svojih Excel formula poštuju europski sustav podataka prema ISO 8601. Prema zadanim postavkama, kalendarski tjedan izračunava se prema američkom sustavu, što dovodi do odstupanja. U SAD -u je prvi dan u godini općenito dodijeljen kalendarskom tjednu 1. Nasuprot tome, u Njemačkoj, Austriji i Švicarskoj prvi kalendarski tjedan općenito se definira kao tjedan koji se kao prvi tjedan u godini sastoji od najmanje četiri dana. U praksi to znači da je 1. tjedan u europskom sustavu tjedan koji uključuje prvi četvrtak u godini.

Za ispravan izračun kalendarskog tjedna možete koristiti funkciju CALENDAR WEEK i argument WEEKSTART funkcije u Excelu. Također je moguće postaviti pojedinačne formule i prilagoditi ih posebno vašoj proračunskoj tablici.