Kolik musíte znát datumových funkcí abyste dokázali v Excelu pracovat s datumy efektivně? Funkce jako ROK, MĚSÍC, DEN a další? Některé datumové úpravy dokáží být velmi komplexní velmi rychle. V dnešním videu si ukážeme, jak můžeme v Excelu pracovat s datumy bez použití jedné jediné excelové funkce.
Excelový soubor ke stažení:
V příkladu máme sloupec s datumy. Naším úkolem je z tohoto sloupce spočítat některé údaje jako začátek měsíce, čtvrtletí, konec roku, začátek čtvrtletí nebo počet dnů v měsíci a názvy měsíců nebo dnů v týdnu. Abychom se těchto veličin v Excelu dopočítali, tak musíme použít většinou kombinaci několika excelových funkcí. A nebo nemusíme?
V tomto videu si vždy nejprve ukážeme, jak to vyřešit pomocí excelových funkcí a následně jak to vyřešit bez jediné funkce pomocí Power Query. Tak jdeme na to.
Začátek měsíce
Pro určení začátku měsíce z datumu použijeme funkci EOMONTH. Ve funkci EOMONTH nejprve označíme datum a jako druhý parametr určíme o měsíc dříve, to znamená -1. Tím dostaneme poslední den předchozího měsíce. Následně k funkci přičteme jedničku a tím získáme první den současného měsíce.
V Power Query získáme první den stejného měsíce tak, že označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Měsíc > Zčátek měsíce.
Konec měsíce
Pro určení konce měsíce z datumu použijeme funkci EOMONTH. V této funkci pouze označíme datum a jako druhý parametr napíšeme nulu, což znamená, že se vrátí poslední den současného měsíce.
Pro určení konce měsíce z datumu nejprve označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Měsíc > Konec měsíce.
Název měsíce
Pokud chceme z datumu vrátit název měsíce slovy, tak použijeme funkci HODNOTA.NA.TEXT. V této funkci označíme v prvním prametru datum a jako formát napíšeme čtyři pímena d. Nesmíme zapomenout na uvozovky, jelikož formát je textová hodnota.
Stejně tak můžeme v Power Query získat i název měsíce. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Měsíc > Název měsíce.
Začátek roku
Z datumu můžeme získat i první den roku. První den roku získáme tak, že použijeme funkci DATUM. V této funkci nejpve použijeme funkci ROK, kde z datumu vyselektujeme rok. A jelikož chceme vrátit první den v roce, tak v parametru měsíc napíšeme jedničku a v parametru den rovněž jedničku. Tím pádem se vrátí první den ve zvoleném roce.
V Power Query velmi lehce získáme i datum prvního dne roku. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Rok > Začátek roku.
Konec roku
Stejný postup můžeme použít pro získání posledního dne v roce z datumu. Opět použijeme funkci DATUM, kde pomocí funkce ROK vyselektujeme rok z datumu, a jelikož poslední den je vždy 12 měsíc a 31 den, tak tyto parametry vyplníme do funkce DATUM. Tím získáme z datumu poslední den v roce.
Stejně tak v Power Query velmi lehce získáme i datum posledního dne roku. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Rok > Konec roku.
Počet dnů v měsíci
Pokud z datumu potřebujeme získat počet dnů v měsíci, tak použijeme nejprve funkci EOMONTH, kde vytáhneme poslední den z aktuálního měsíce. Když známe datum posledního dne v měsíc, tak z něho vyselektujeme den pomocí funkce DEN. Tím získáme počet dnů v měsíci.
V Power Query získáme i počet dnů v měsíci bez jediné funkce. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Měsíc > Dny v měsíci.
Čtvrtletí
Když potřebujete zařadit datum do čtvrtletí, tak použijte funkci MĚSÍC. V této funkci označíme datum a to vydělíme třemi, jelikož chceme získat kvartály. A nakonec funkce MĚSÍC zabalíme do funkce ZAOKR.NAHORU.
Pořadové číslo čtvrtletí z datumu získáme v Power Query tak, že označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Čtvrtletí > Čtvrtletí roku.
Začátek čtvrtletí
Získat první datum kvartálu z datumu je trochu komplikovanější. Začneme funkcí DATUM, kde nejprve stanovíme rok a rok stanovíme pomocí funkce ROK, a rok určujeme z datumu. Následně musíme stanovit měsíc, a to měsíc prvního čtvrtletí, na prvním řádku čtvrtletí začíná 1.4.2026. Takže použijeme kombinaci funkcí. Funkce MĚSÍC vrátí pořadové číslo měsíce z datumu. Na prvním řádku u května se tedy vrátí číslo pět. Následně od této funkce musíme odečíst funkci MOD, což je funkce, která vrátí zbytek po vydělení. Ve funkci MOD opět použijeme funkci MĚSÍC, která opět vrátí číslo pět, teď od toho odečteme jedničku a jako dělitel použijeme trojku, jelikož nám jde o čtvrtletí. Tato část funkce zajistí, že zjistíme, o kolik měsíců jsme vzdáleni od začátku čtvrtletí. A jako den použijeme jedničku, jelikož chceme vrátit první den čtvrtletí.
Na rozdíl od Excelu velmi lehce v Power Query získáme i první datum příslušného čtvrtletí. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Čtvrtletí > Začátek čtvrtletí.
Konec čtvrtletí
Podobný postup použijeme i pro konec čtvrtletí. Opět začneme funkcí DATUM. Nejprve funkce ROK vytáhne rok ze zadaného data. Potom pomocí funkce MĚSÍC zjistíme číslo měsíce. Klíčová část je funkce MOD, která spočítá, kolik měsíců jsme vzdáleni od začátku aktuálního čtvrtletí. Například pokud máme květen, tedy měsíc číslo 5, funkce zjistí, že jsme jeden měsíc od začátku čtvrtletí, protože druhé čtvrtletí začíná v dubnu. Následně se funkce vrátí na první měsíc čtvrtletí a přičte dva měsíce, čímž získá poslední měsíc čtvrtletí. Pro květen tak dostaneme červen. Funkce DATUM následně vytvoří datum prvního dne tohoto měsíce. A nakonec funkce EOMONTH vrátí poslední den daného měsíce.
Stejně tak velmi lehce v Power Query získáme i poslední datum příslušného čtvrtletí. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Čtvrtletí > Konec čtvrtletí.
Týden roku
Pro získání pořadového čísla týdne použijeme funkci WEEKNUM. Ve funkci WEEKNUM označíme datum a nesmme zapomenout vyplnit i druhý parametr, kde určíme že týden začíná pondělím.
Pořadové číslo týdne v roce v Power Query získáme tak, že označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Týden > Týden roku.
Den v týdnu
Pomocí funkce DENTÝDNE můžeme určit, o jaký den v týdnu se jedná. Funkce vrátí hodnoty od jedné do sedmi, podle toho, zda se jedná o pondělí, úterý nebo třeba neděli. Ve funkci DENTÝDNE označíme datum a opět nesmíme zapomenout vyplnit druhý parametr, kde určíme, že týden začíná pondělím.
Stejně tak získáme v Power Query i pořadové číslo dne v týdnu. Označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Den > Den týdne. Tím se vrátí pořadové číslo dne. Nicméně Power Query čísluje od nuly, takže pondělí má nulu a neděli má číslo šest. Pokud chceme vrátit stejné pořadové číslo jako v Excelu, tedy d jedné do sedmi, tak k funkci v příkazovém řádku ještě přičteme jedničku.
Název dne
Stejně jako můžeme pomocí funkce HODNOTA.NA.TEXT vrátit název měsíce, tak můžeme pomocí této funkce vrátit i název dne. Stačí v parametru formát použít čtyři písmen d.
Pokud chceme z datumu získat název dne v týdnu, tak označíme sloupec s datumem a na kartě Přidání sloupce vybereme Datum > Den > Název dne.
Jedna odpověď
👍👍👍