Nastavení dne v týdnu podle data v aplikaci Microsoft Excel. Jak přidat a odečíst data, dny, týdny, měsíce a roky v Excelu Excel den v týdnu od čísla

Potřeba určit a zobrazit číslo týdne podle data vzniká velmi často. To platí zejména pro ty lidi, kteří jsou zapojeni do různých reportáží. Například připravují krátká shrnutí týdenní práce pro vedení.

Musíte si pamatovat, že existuje alespoň několik přístupů k číslování čísla týdne.

1. První přístup používá Rusko. Podle standardu GOST ISO 8601—2001 první pracovní týden Uvažuje se rok, na který připadá první čtvrtek v roce.
2. Druhý přístup spočívá v tom, že týden, na který připadá 1. leden, je považován za první týden v roce.

Ještě jednou připomeňme, že v Rusku se využívá první z výše popsaných výšlapů.

CELÉ ČÍSLO((A1 -DATUM(ROK(A1 -DEN V TÝDNU(A1 -1)+4);1,3)+DEN V TÝDNU(DATUM(ROK(A1 -DEN V TÝDEN(A1 -1)+4);1,3))+ 5)/7)

Při použití anglické verze Vzorec Excel vypadá jako:

INT ((A1 -DATUM (ROK (A1 -DEN V TÝDNU (A1 -1)+4),1,3)+DEN V TÝDNU (DATUM (ROK (A1 -DEN V TÝDNU (A1 -1)+4),1,3))+ 5)/7)

Pokud stále potřebujete druhý přístup, použijte vzorec k určení čísla týdne:

1+INTEGROVANÉ ČÍSLO((A1 -DATUM(ROK(A1 +4-DEN V TÝDNU(A1 +6)),1,5)+DEN V TÝDNU(DATUM(ROK(A1 +4-DEN V TÝDNU(A1 +6)),1,3) ))/7)

vzorec pro anglickou verzi:

1+INT ((A1 -DATUM (ROK (A1 +4-WEEKDAY (A1 +6)),1,5)+WEEKDAY (DATE (YEAR (A1 +4-WEEKDAY (A1 +6)),1,3) ))/7)

Ve všech výše uvedených vzorcích změníme A1 na datum nebo odkaz na datum.

Všechno je mnohem jednodušší, pokud používáte Excel 2007 nebo 2010 . Počínaje těmito verzemi tam bylo speciální funkce, což vám umožní určit číslo týdne podle data. (Poznámka: tato funkce může být povolena v aplikaci Excel 2003, k tomu je třeba připojit doplněk Balíček analýzy přes menu Servisní doplňky, ale v tomto případě, pokud dokument otevře osoba, která nemá toto nastavení povolené, tak místo čísla týdne uvidí chybu #HODNOTA!, takže to v Excelu 2003 nepoužijeme)

Funkce je volána ČÍSLO TÝDNE (v anglické verzi WEEKNUM). Syntaxe je velmi jednoduchá.

WEEKNumber(Datum; Typ)

Druhý parametr Typ je velmi důležitý, protože standardně se používá druhý z výše popsaných přístupů k číslu začátku týdne.

Pro Rusko musíte použít vzorec v následujícím formátu.

ČÍSLO TÝDNE(A1; 21)

V tomto případě typ 21 znamená, že za číslo prvního týdne bude považováno to, na které připadá první čtvrtek v roce a první den v týdnu pondělí (poznámka: v mnoha zemích je neděle považována za první den v týdnu)

Opravdu doufáme, že vám náš článek pomohl při řešení vašeho problému. Budeme vděční, když kliknete +1 a/nebo Mám rád v dolní části tohoto článku nebo sdílejte s přáteli pomocí tlačítek níže.

webové stránky – Neomezené možnosti Excelu

Funkce DAY vrací den, který odpovídá konkrétnímu datu. Výsledkem funkce je číslo z rozsahu od 1 do 31.

Funkce WEEKDAY vrací den v týdnu, který odpovídá konkrétnímu datu. Výsledkem této funkce je číslo z rozsahu od 1 do 7 (jedna odpovídá neděli a sedm sobotě).

Funkce DAYS se vrátí číselná hodnota, charakterizující rozdíl ve dnech mezi dvěma danými daty.

Vlastnosti syntaktického zápisu funkce DAY, WEEKDAY a DAYS v Excelu

Funkce DAY má následující syntaxi:

DEN(datum)

Vezme jako vstup jediný parametr, datum, který odpovídá datu, jehož den chcete určit.

Poznámky:

1. Vstupním parametrem je obvykle buňka z Excel tabulky, obsahující data ve formátu data.
2. Jako vstup jsou přijímány také výsledky výpočtů funkce DATE a dalších funkcí, které vracejí hodnoty ve formátu Date.
3. Při přímém zadávání dat mezi hranaté závorky (například =DAY(“06/24/2018”)) musíte použít uvozovky, které označují, že data předaná vstupu jsou typu Text. Dále Excel automaticky převede data do formátu Datum.
4. Chyba #HODNOTA! Bude vygenerováno, pokud byla na vstup funkce DAY předána data nepodporovaného typu Excel, například: DAY(“24. června 2018”) nebo DAY(“06/24/2018”).
5. Parametr datum může být reprezentován jako číslo v časovém kódu aplikace Excel.

Funkce WEEKDAY má následující syntaxi:

WEEKDAY(datum;[typ])

Popis argumentů funkce:

1. Datum je povinný parametr odpovídající datu, jehož den v týdnu chcete určit. Tento parametr má vlastnosti popsané v poznámkách k předchozí funkci.
2. Typ – číslo v rozsahu od 1 do 3, které označuje, který den v týdnu je považován za první (v některých zemích je prvním dnem v týdnu neděle, u nás pondělí). Pokud typ=1, první den v týdnu bude neděle, typ=2 – pondělí. Pokud je typ=3, výsledkem funkce WEEKDAY bude číslo v rozsahu od 0 do 6, kde 0 odpovídá pondělí.

Poznámka: v některých případech je místo číslic 1,2...7 vyžadován název dne v týdnu (pondělí, úterý...neděle). Chcete-li to provést, můžete vytvořit následující tabulku:

A poté zadejte textovou hodnotu odpovídající dni v týdnu:

Pro tento úkol však můžete použít následující funkci: =TEXT(A2,"dddd").

Výsledkem této funkce je text „Neděle“. Pokud je vyžadován krátký záznam dne v týdnu (So, Po, St), měl by být jako druhý parametr specifikován „dddd“.

Funkce DAYS se používá k výpočtu počtu dní mezi dvěma zadanými daty a má následující syntaxi: =DAYS(datum_konce, datum_zahájení).

Popis argumentů této funkce:

1. End_date je povinný parametr charakterizující datum ukončení události.
2. Start_date je povinný parametr charakterizující datum zahájení určité události pro provedení výpočtu.

Poznámky:

1. Výpočet počtu dní mezi daty převede data na čísla v časovém kódu aplikace Excel. To znamená, že položky „=DAYS(“06/24/2018”;”06/13/2018”)” a “=DATEVALUE(“06/24/2018”)-DATEVALUE(“06/13/2018”) “ vrátí stejné hodnoty.
2. Pokud jeden z parametrů funkce představuje datum zapsané jako text, bude zpracován funkcí DATEVALUE, která vrátí celé číslo.
3. Pokud jsou parametry funkce čísla, jejichž hodnoty jsou mimo hodnoty povolené v časovém kódu aplikace Excel, bude vygenerována chyba #HODNOTA!
4. Pokud funkce DNY používá parametry jako data, která nelze v aplikaci Excel reprezentovat jako data, bude vrácena chyba #HODNOTA!

Příklady výpočtů v Excelu pomocí funkcí DEN, WEEKDAY a DAYS

Tabulka obsahuje několik dat zapsaných ve formátu Excel data, ve kterém musí být zaměstnancům určitého podniku vyplácena mzda. Vzhledem ke změnám v legislativě se mzdy zaměstnanců musí počítat vždy k prvnímu dni v měsíci. Je nutné opravit termíny od 15.7.2018 do 1.7.2018. Část tabulky vypadá takto:

K řešení použijeme následující vzorec: =B3-DEN(B3)+1.

Stejným způsobem vyplníme zbývající sloupce. V důsledku toho získáme nový sloupec se správnými daty výplaty mezd:

Určení pracovních dnů a víkendů pomocí vzorce WEEKDAY

Příklad 2 Softwarečte data z excelové tabulky a používá je ve svých výpočtech. Jedna z buněk obsahuje údaje ve formátu Datum odpovídající datu přijetí zásilky. Je nutné zjistit, zda uvedené datum odpovídá pracovnímu dni v týdnu (Po až Pá) a vrátit logickou hodnotu „true“ nebo „false“ (pokud se jedná o volný den) pro další zpracování programem.

Původní tabulka vypadá takto:

Pro určení dne v týdnu použijeme WEEKDAY, jehož první argument je údaj z buňky „Datum příjezdu“ a druhý je 2 (počítání dnů v týdnu začíná od jedné, což odpovídá pondělí ). K vyřešení použijeme následující funkci:

Jak vidíte, všechna data jsou pracovní dny v týdnu.

Proč potřebujete funkci DAYS v Excelu?

Příklad 3. Je dobře známo, že druhá světová válka začala 22. června 1941 a skončila 9. května 1945. Je nutné určit, kolik dní probíhalo nepřátelství.

Do tabulky zadáme počáteční údaje:

K výpočtu použijeme následující vzorec: =DAYS(B3,A3).

Dostaneme následující výsledek:

To znamená, že nepřátelské akce trvaly 1417 dní.

Poznámka! Jakékoli datum v Excelu je počet dní, které uplynuly od data 01.01.1900. Další podrobnosti jsou popsány v článku: Jak vypočítat datum v Excelu? Proto můžete libovolně používat takový jednoduchý vzorec bez funkcí: =B3-A3.

Jak můžete vidět na obrázku, výsledek je podobný. Ve skutečnosti není funkce DAYS v Excelu vůbec potřeba.

Když vyvstane otázka ohledně získávání informací o dni v týdnu pro zadané datum, může být poměrně obtížné jej určit.

Listovat stovkami stránek kalendáře při hledání data může být docela únavné. Tento problém lze snadno vyřešit software Excel – doslova za pár sekund. Jak? Podívejme se na způsoby, jak určit den v týdnu z data v Excelu.

Software umožňuje určit den v týdnu několika způsoby - může to být použití několika funkcí a formátování vstupního pole - zde, jak se říká, je to věc vkusu.

1. Určení dne v týdnu pomocí formátování dat.

Den v týdnu najdete na pár kliknutí tak, že zformátujete buňku, do které byla zadána původní data. Zadejme například datum 9. dubna 2017 a podívejme se, jaký byl den v týdnu.

Chcete-li změnit formát buňky, musíte zadat buňku, do které jsme zadali data, a vyvolat kontextovou nabídku - „Formát buňky“.

V zobrazeném dialogovém okně Formát buňky vyberte kartu „Číslo“ - „Všechny formáty“ a zadejte možnost formátování „DDDD“, po které můžete operaci použít.

Poté vidíme, že se v buňce objeví požadovaný den v týdnu a zadané datum zůstane v řádku vzorců.

Možnost formátování lze změnit, například pokud zadáte „DDD“, pak místo celého názvu dne v týdnu bude pouze jeho zkratka.

2. Určete den v týdnu pomocí funkce "TEXT".

První metoda nemůže zajistit výstup dat do konkrétní buňky nebo rozsahu dat. K tomu použijte funkci „TEXT“.

Zadejte například stejné datum, 9. dubna 2017, a v další buňce zavolejte „Průvodce funkcí“ a vložte našeho operátora.

V průvodci funkcí definujeme kategorii - vybereme „Text“ a přímo samotný operátor „TEXT“.

V okně pro zadávání argumentů funkce můžete zadat pouze dva argumenty - „hodnota“ a „formát“.

Do „Value“ zadáváme adresu, kam byly zapsány původní údaje, do „Formát“ – tedy formát dne v týdnu – „dddd“ pro plné zobrazení názvu dne a „ddd“ pro zkrácený, stejně jako v předchozím způsobu.

Poté může použít provedení funkce.

Po použití operátoru “TEXT” vidíme, že se ve vybrané buňce správně zobrazil den v týdnu, který byl určen podle zdrojových dat.

Správnost funkce lze zkontrolovat změnou zdrojových údajů, například pokud změníme číslo, uvidíme, že se změnil i den v týdnu.

3. Určení dne v týdnu pomocí funkce „WEEKDAY“.

kromě předchozí metody definice dne v týdnu, Excel má speciální operátor, který se s úkolem snadno vyrovná.

Funkce „WEEKDAY“ na rozdíl od předchozích metod může zobrazit pořadové číslo dne v týdnu pro libovolné požadované datum, stačí zadat pořadí číslování - od pondělí nebo od neděle;

Podívejme se v praxi. Označíme datum, pro které potřebujeme určit den v týdnu, a vložíme funkci pomocí „Průvodce funkcí“.

V dialogovém okně musíte přejít do kategorie „Datum a čas“ a v ní najít požadovaný operátor „WEEKDAY“. Jeho funkce vypadá takto: "=WEEKDAY(date_v_numeric_format, [type])".

Vyplňte argumenty funkce „WEEKDAY“ - k tomu místo data uvedeme konkrétní datum nebo adresu buňky, kde se datum nachází, a v „typu“ uvedeme číslo od „ 1“ až „3“, přičemž je třeba vzít v úvahu následující:

„1“ - číslování začíná od neděle pořadovým číslem „1“;
„2“ - číslování začíná v pondělí pořadovým číslem „1“;
„3“ - číslování začíná v pondělí pořadovým číslem „0“.

Jak vidíte, v zadané buňce se zobrazilo číslo „3“, které podle zadaného argumentu „typ“ - „2“ - odpovídá prostředí.

Při změně zdrojových dat funkce funguje správně a správně zobrazuje pořadové číslo dne v týdnu.

Jak vidíme, určení dne v týdnu zadaným datem nevyžaduje mnoho úsilí a vyžaduje jen několik sekund a způsob určení je ponechán na volbě uživatele.

Tento článek obsahuje několik užitečných vzorců, které vracejí konkrétní datum.

Určení dne v roce

1. leden je prvním dnem v roce a 31. prosinec posledním. A co zbytek dnů mezi tím? Následující vzorec vrátí den v roce pro datum uložené v buňce A1: =A1-DATE(YEAR(A1),1,0) . Pokud například buňka A1 obsahuje datum 16. února 2010, vzorec vrátí 47, protože toto datum je 47. den v roce.

Následující vzorec vrátí počet dní zbývajících v roce od určitého data (za předpokladu, že je v buňce A1): =DATE(YEAR(A1),12,31) .

Určení dne v týdnu

Pokud potřebujete určit den v týdnu pro datum, funkce VŠEDNÍ DEN se s tímto úkolem vyrovná. Funkce bere jako argument datum a vrací celé číslo od 1 do 7 odpovídající dni v týdnu. Následující vzorec například vrátí 6, protože první den roku 2010 je pátek: =WEEKDAY(DATE(2010,1,1)) .

Funkce VŠEDNÍ DEN také používá volitelný druhý argument označující systém číslování dnů pro výsledek. Pokud zadáte 2 jako druhý argument, funkce vrátí 1 pro pondělí, 2 pro úterý atd. Pokud zadáte 3 jako druhý argument, funkce vrátí 0 pro pondělí, 1 pro úterý atd. .

Můžete také definovat den v týdnu pro buňku obsahující datum pomocí vlastního číselný formát. Buňka v následujícím formátu zobrazuje den v týdnu: DDDD. Mějte na paměti, že buňka ve skutečnosti obsahuje celé datum, nikoli pouze číslo dne.

Určení data poslední neděle

Vzorec v této části vrací poslední zadaný den. K získání data minulé neděle můžete použít následující vzorec. Pokud je aktuálním dnem neděle, vzorec vrátí aktuální datum. Výsledek bude sériové číslo data (buňku je třeba naformátovat, aby se zobrazilo čitelné datum): =DNES()-REST(DNES()-1;7) .

Chcete-li tento vzorec upravit tak, aby našel datum v jiný den než v neděli, změňte 1 na jiné číslo v rozsahu 2 (pondělí) až 7 (sobota).

Určení dne v týdnu po datu

Následující vzorec vrátí zadaný den v týdnu, který nastane po určitém datu. Tento vzorec můžete použít například k určení data prvního pátku po 4. červenci 2010. Vzorec předpokládá, že buňka A1 obsahuje datum a buňka A2 obsahuje číslo od 1 do 7 (1 odpovídá neděli, 2 až pondělí atd.): =A1+A2-WEEKDAY(A1)+(A2)

Pokud buňka A1 obsahuje 4. července. 2010 a buňka A2 obsahuje b (což představuje pátek), potom vzorec vrátí 9. července 2010. Toto je první pátek po 4. červenci 2010 (den, který připadá na neděli).

Nalezení n-tého konkrétního dne v týdnu v měsíci

Možná budete potřebovat vzorec k nalezení data konkrétního dne v týdnu. Řekněme, že výplatní den vaší společnosti je každý druhý pátek v měsíci a musíte je určit
platební dny za každý měsíc v roce. Následující vzorec provede požadovaný výpočet:
=DATUM(A1,A2,1)+A3-WEEKDAY(DATE(A1,A2,1))+(A4-(A3>=WEEKDAY(DATE(A1,A2,1))))*7

Tento vzorec předpokládá, že:

• buňka A1 obsahuje rok;
• buňka A2 obsahuje měsíc;
• buňka A3 obsahuje číslo dne (1 - neděle, 2 - pondělí atd.);
• buňka A4 obsahuje číslo – například 2 – označující druhý výskyt dne v týdnu uvedeného v buňce A3.

Při použití tohoto vzorce k určení data druhého pátku v červnu 2010 by výsledek byl 11. června 2010.

Určení posledního dne v měsíci

Chcete-li určit datum, které odpovídá poslednímu dni v měsíci, můžete použít funkci DATUM. Musíte však zvýšit měsíc o 1 a nastavit hodnotu dne na 0. Jinými slovy, „0.“ den příští měsíc- Toto je poslední den aktuálního měsíce.

Následující vzorec předpokládá, že datum je uloženo v buňce A1. Vzorec vrátí datum, které odpovídá poslednímu dni v měsíci: =DATUM(ROK(A1),MĚSÍC(A1)+1,0) .

Tento vzorec můžete upravit a určit, kolik dní daný měsíc zahrnuje. Následující vzorec vrátí celé číslo, které odpovídá počtu dní v měsíci pro datum v buňce A1 (buňku naformátujte jako číslo a ne jako datum): =DAY(DATE(YEAR(A1),MONTH). (A1)+1, 0))

Určení čtvrtletí data

U finančních výkazů může být užitečné prezentovat informace podle čtvrtletí. Následující vzorec vrátí celé číslo mezi 1 a 4, které odpovídá kalendářnímu čtvrtletí pro datum v buňce A1: =ROUND UP(MONTH(A1)/3,0) . Tento vzorec vydělí číslo měsíce 3 a pak zaokrouhlí výsledek.