Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce » ms-excel-datum-a-cas

Datum a čas - funkce prakticky

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Praktické příklady na funkce datum a čas v Microsoft Excelu - článek je průběžně doplňován a rozšiřován.

15.12.2015: Doplněno o Průměrná rychlost a Jak určit čtvrtletí

Úvodem aneb co se v článku dozvíte

Cílem tohoto článku je na praktických ukázkách předvést použití funkci datum a čas na praktických příkladech (většinou se jedná o dotazy, které jsem zodpovídal). Pro přehlednost je článek rozdělen do kapitol:

Trocha teorie úvodem

Datum je celé číslo, čas desetiné číslo a tady nastávají problémy při práci s datumem a časem v Excel.

Když profesionálně řešíte v Microsoft Excelu rozsáhlejší (někdy i méně rozsáhlé ) úkoly ekonomického rázu, neobejdete se bez informace o datu a čase (hypotéky, kalendáře, docházky, kniha jízd, atd.).

Microsoft Excel nabízí množství funkcí pro práci s datem a časem, jen musíme vědět jak je využít. Jelikož mívám opakující se dotazy na toto téma, rozhodl jsem se publikovat tento článek. Snad Vám usnadní orientací v této problematice ukázkové praktické příklady.

Předpokládané znalosti

Pro pochopení článku doporučuji znalost syntaxe funkcí datum a čas a jak formátovat buňku. Obojí je přehledně sepsáno v mých článcích:

Poznámka: Můžete pokračovat dále bez znalosti, když praktické příklady nepochopíte můžete se k článkům vrátit.

Datum a čas je prosté číslo

Excel používá sytém 1900 nebo 1904. Přednastavený je systém 1900. Proto se v tomto článku budu zabývát tímto systémem. Princip je stejné jen pro 1904 je první den posunt.

Takže v systému 1900 je číslu 1 přiřazeno datum 1.1.1900. číslu 2 je přiřazeno datum 2.1.1900, atd. až do 31.12.9999 což je číslo 2.958.465. Poznámka: Excel starších verzí než 2003 možná neumí plný rozsah, ale předpokládám, že nikdo nepoužívá starší verze.

Doporučení. Nemíchejte v jednom sešitu systémy 1900 a 1904.

Podobně to je s časem, tomu odpovídá desetinné číslo 0,000 - 1,000. Takže 12:00 hod odpovídá 0,5. Jedné minutě odpovídá 1/(24*60). 24 hodin po 60 minutách. Jedné vteřině odpovídá 1/(24*60*60).

Bohužel Excel neumí se standartími funkcemi pracovat s datem před rokem 1900. Takže pro datum 12.12.1212 nelze zjistit zda bylo pondělí nebo středa. Existují sice VBA aplikace, které s tímto pomohou, ale je potřeba je do Excelu nainstalovat.

Jak z čísla vytvořit datum

Změnou formátu.

Jak změnit formát jsme popsal v článku Formáty pro datum a čas v MS Excelu

Excel umí automaticky rozpoznat datum. Napíšemeli 1.1.1900 automaticky zapíše 1 a změní formát buňky na "datum a čas".

Zápisy, které Excel převede na datum.

  • 12.2.2011
  • 12/2/2011
  • 12-2-2011
  • 12-2/2011
  • 12.únor.2011
  • 12-únor-2011
  • 12.2.11
  • 12.2.
  • 12-2
  • 12/2
  • 12.únor

Poznámka: Chceme-li mít datum zapsán jako text a ne číslo s formátem datum a čas. Je nutno buňce přiřadit formát text. Nevýhodou je, že již nebudeme moci matematicky pracovat s tímto datem.

Jak z desetiného čísla vytvořit čas

Patřičnou změnou formátu.

Excel umí automaticky rozpoznat čas. Napíšemeli 12:00 automaticky zapíše 0,5 a změní formát buňky na "datum a čas".

Zápisy, které Excel převede na čas:

  • 12:12:12
  • 11:11:11 AM
  • 3:30 PM
  • 15:30

Poznámka: Chceme-li mít čas zapsán jako text a ne číslo s formátem datum a čas. Je nutno buňce přiřadit formát text. Nevýhodou je, že již nebudeme moci matematicky pracovat s tímto časem. AM - dopolende. PM - odpoledne.

Zajímavost: Pokud vložíte čas 88:00:12, Excel tento čas převede na příslušný počet dnů. Největší čas který přepočítá je 9999:59:59 větší čas poté Excel považuje za text.

Vlastní formát datum, čas

Excel sice přiřazuje formát datumu a času automaticky, ale to můžete změnit. Nastavením vlastního formátu v dialogovém okně Formát buněk

Dialogové okno Formát buněk je k dispozici přes:

  • Pravé tlačítko myší - klik na buňce a poté Formát buněk
  • Karta Domů sekce Číslo čtvereček se šipkou.

Následně vybrat záložku číslo

Datum čas znaky

Formát data

Pro formát data se používají:

  • d,dd,ddd,dddd
  • m,mm,mmm,mmmm měsíce jako římské číslice (I až XII) - problém v české verzi se zobrazením Římských číslic
  • rr,rrrr - v anglickou (EN) verzi Excelu yy, yyyy
  • h,hh
  • m,mm - minuty bohužel mají stejný název jako měsíce
  • s,ss

Případně využít další možnosti (složené závorky, ...)

  • h:mm:ss.00 - zlomky vteřin
  • [mm] - uplynulý čas v hodinách
  • [h] - uplynulý čas v minutách
  • h:mm dop./odp. - zlomky vteřin

Poznámka

Pokud zadáte formát [h] při sečtení 20 hodin + 20 hodin bude výsledek 40 hodin. Při klasické h bude výsledek plus den a 16 hodin. Podobně funguje i u minut (sekund).

Problémem je zadávání římských čísel měsíců přes formát. Excel 2007 a 2010 má totiž na WINDOWS 7 problém s formátem mmm, proto je potřeba využít funkce ROMAN.

Více o vlastním formátu v samostatném článku: Jak na vlastní formát v Excelu.

Klávesové zkratky pro vkládání

Budete-li vkládat aktuální datum, čas, nemusíte jej vypisovat. Stačí použít klávesovou zkratku:

  • Ctrl + ; – vloží aktuální datum podobně jako funkce =DNES()
  • Ctrl + Shift + : – aktuální čas

Pokud budete chtít, aby došlo k automatické změně na aktuální datum, čas při přepočtu listu je notno použít fukce:

=DNES() =NYNÍ()

Aktuální datum čas

Předpokládám, že systémový datum a čas máte ve vašem počítači nastaven správně.

Jednou z důležitých vlastností co Excel umí je, že zobrazí aktuální čas. Hodí se například pro faktury (víme, kdy se tiskly) či pro cestovní zprávy (omezíme zneužívání, dokument bude mít aktuální datum a čas tisku. Zobrazení aktuálního času:

  • =DNES() – zobrazí pouze aktuální datum
  • =NYNÍ() – zobrazí aktuální datum s časem
=DNES() =NYNÍ()

Aktuální datum lze vložit klávesovou zkratkou Ctrl+; a aktuální čas Ctrl+Shift+:. Nebude se při následnem přepočtu listu měnit.

Poznámka: Co je v buňce zobrazeno lze změnit formátem buňky.

Kolik času zbývá?

Při plánování složitějších úloh je vhodné vědět kolik času nám do odevzdání zbývá. Stačí k tomu jednoduchá funkce (vzorec):

  • =ROK360(DNES();"27.9.2027") - výsledkem počet zbývajících dnů

Kde argumenty znamenají:

  • DNES() - aktuální datum (dnešní)
  • "27.9.2027" - od tohoto data se odečítá
  • Výsledek - je počet zbývajících dnů

Pořadové číslo dne, měsíce, ...

DEN

Následující ukázkové příklady řeknou pořadové číslo dne, který je uveden ve funkci:

  • =DEN("15-Dub-1993") - výsledek - 15
  • =DEN("8.11.93") - výsledek - 8
  • =DEN("8/11/93") - výsledek - 8

TÝDEN

Syntaxe je u tohoto trošinku složitější:

  • DENTÝDNE(datum;typ)

Kde argument znamená:

  • Datum opět se uvede nějaké datum
  • Typ
    • 1 - nebo neuvedeno. Čísla od 1 (neděle) do 7 (sobota).
    • 2 - Čísla od 1 (pondělí) do 7 (neděle).
    • 3 - Čísla od 0 (pondělí) do 6 (neděle).

Příklad: =DENTÝDNE("12.12.12") - výsledek - 4

MĚSÍC

Podobně jako předchozí vrátí pořadové číslo měsíce =MĚSÍC("12.12.12")

Podobně pro minutu MINUTA(čas), rok ROK(datum)

Počet dnů měsíců mezi dvěma daty

Kolik dnů měsíců jste na světě? (stačí zadat datum narození)

C3 - např. datum vašeho narození

C4 - např. dnešní datum

  • =DATEDIF(C3;C4;"y") - počet let
  • =DATEDIF(C3;C4;"m") - počet měsíců
  • =DATEDIF(C3;C4;"d") - počet dnů

POZOR: C4 > C3

DATEDIF
=DATEDIF($D$7;$D$6;"y") =DATEDIF($D$7;$D$6;"m") =DATEDIF($D$7;$D$6;"d")

Poslední den v měsíci

Který je poslední den v měsíci

V tomto měsící, pomůžeme si funkci DNES(), funguje i s funkci NYNÍ()

=EOMONTH(DNES();0)

V měsíci kterého den zadáme, klidně se můžeme odkazat do buňky ve kterém máte datum (např. B1)

=EOMONTH("6.2.2016";0)

Poslední den v příštím měsíci.

=EOMONTH("6.2.2016";1)

Datum z pořadového dne v roce

Jak z pořadového čísla dne v daném roze získat daný datum ve formátu den.měsíc.rok

=DATUM(2016;1; pořadové číslo dne ) =DATUM(2016;1;1) ... bude 1.1.2016 =DATUM(2016;1;32) ... bude 1.2.2016 Datum z pořadového dne v roce

Jak dlouho trvalo?

Počet hodin mezi dvěma časy. Využití pro docházkový list, kalendář, Grantův diagram).

Další častý dotaz. Jak spočítat počet hodin. Využití například v docházkovém listu. Zadá se příchod a odchod pracovníka a Excel dopočte pročet odpracovaných hodin. Maximální délka 24 hodin. Předpoklad - nikdo nemlže odpracovat v jenom dnu více než 24 hodin.

Počet hodin - 1 den

Jak slouho trvalo - datum a čas

Jednoduchý odečet.

=C10-B10

Nastavení formátu:

  • číslo - zobrazí desetinné číslo (tak jak je fyzicky uložen čas v Microsoft Excelu)
  • h:mm
  • [m] - celkový počet minut

Počet hodin - přes půlnoc

Jak slouho trvalo - datum a čas
=(C25-B25+(C25 < B25))

Nebo na první pohled možná jasnější:

=KDYŽ(C25 < B25;1-B25+C25;C25-B25)

Počet hodin - včetně přestávky

Jak slouho trvalo - datum a čas
=( E42-B42+(E42<B42) - (D42-C42+(D42<C42)) )

Z času vypočíst mzdu (plat)

Jak na výpočty mzdy na základě odpracovaných hodin? Pokud nevíte, dozvíte se v této kapitole.

Příklad 1

Jak z časových údajů vypočíst peníze. Z předchozí kapitoly umíte zjistit čas strávený práci v daném dnu (0 - 23:59:59 hod). V tomto ukázkovém příkladu máte již k dispozici hodinovou sazbu a potřebujete vypočíst mzdu (plat) za odpracované hodiny.

Využijeme funkce MINUTA, HODINA.

=HODINA(B11) =MINUTA(B11)/60 Z času vypočíst mzdu - Excel

Poznámka : Hodinová mzda je zadána v každé buňce. V případě možností může být hodinová sazba zadána v jedné jediné buňce a odkazovat se na tuto buňku.

Poznámka 2: Příklad 2 je jen rozšíření Příkladu 1. Viz soubor ke stažení zdarma.

Příklad 3

Jak na výpočet pokud, máte k dispozici celkové odpracované hodiny (například za celý týden, měsíc). Opět to není nic těžkého. Stačí použít funkce HODNOTA.NA.TEXT a MINUTA:

=HODNOTA.NA.TEXT(B28;"[h]")+MINUTA(B28)/60 Z času vypočíst mzdu - Excel

Příklad 4

Potřebujete-li z hodnot v buňce vytvořit text (např. do výplatního lístku, faktury za služby, vykonanou práci). Můžete využít funkci CONCATENATE:

=CONCATENATE("Za odpracovaný ";C33; " hodin Vám naleží: ";E33;" ,- Kč") Z času vypočíst mzdu - Excel

Ke stažení zdarma

Soubor Jak z času vypočíst plat/mzdu. soubor ve formátu *.xlsx ke stažení zdarma pro Excel 2010 (2007).

Soubor Jak z času vypočíst peníze (plat/mzdu). soubor ve formátu *.xls ke stažení zdarma pro Excel 2003 (97).

Poznámka: Jeden ze zajímavých dotazů, proto jsem jej zpracoval do jedné kapitoly v článku ať není zbytečně schován v komentářích. Z dotazů vím, že komentáře se moc nečtou (obsahují odpověď na kladený dotaz).

Zpět na seznam kapitol.

Vytvořit datum z čísel

Vytvořit datum z čísel

  • =DATUM(2007;12;10) - výsledek - 39246

Toho využijeme u speciálních složitějších funkcí, případně až budeme programovat ve VBA.

Vypočet velikonočního pondělí

Poznámka nejde o můj vzorec, našel jsem jej na internetu. Bohužel nikdo neuvádí autora, který je asi neznámý. Stačí zadat do google "Excel velikoneční pondělí" a najdete desítky různých odkazů.

Do buňky A1 zadat rok a nádledující vzorec zadejte do buňky ve které chcete mít velikonoční pondělí:

=(KČ(("4/"&A1)/7+MOD(19*MOD(A1;19)-7;30)*14%;)*7-6)+1

Poznámka: Pokud vám vychází velké číslo, je třeba změnit formát buňky.

Počet pondělku (i jiných dnů) v měsíci nebo mezi dvěma daty

V komentářích byl zajímavý dotaz: "jak pouzit funkci napr. k tomu kolik je úterků v daném měsíci, nebo v rozpětí dvou datumů, popř. jiných dnů jako čtvrtků, sobot". Jelikož se jedná o zajímavý dotaz, doplnil jsme článek

Počet pondělků (jiných dnů) v zadaném měsíci

=CELÁ.ČÁST((EOMONTH(C10;0)-C11)/7)-CELÁ.ČÁST((C10-C11)/7)+CELÁ.ČÁST(DENTÝDNE(C10-C11)/7)
  • C10 ... první den v daném měsíci
  • C11 ... hledaný den (2...Pondělí)

Počet pondělků (jiných dnů) mezi dvěma daty

=CELÁ.ČÁST((DENTÝDNE(C4-C6)-C4+C5)/7)
  • C4 ... první den
  • C5 ... poslední den
  • C6 ... hledaný den (2...Pondělí)
Pocet pondelku - Excel

Počet dnů v měsíci

V A1 je jakýkoli den v měsící ve kterém hledáme počet dnů:

=DEN(EOMONTH(A1;0))

pro starší verze Excel (bez funkce EOMONTH)

=DEN(DATUM(ROK(A1);MĚSÍC(A1)+1;))

Kód není z mé hlavy, ale nevím kde jsem jej získal.

Velká písmena dnů měsíců

Jak zajistit automaticky velká písmena názvů měsíců a dnů? Při použití formátu umí vloží Excel jen malé. Stačí použít funkci VELKÁ2 a HODNOTA.NA.TEXT a je hotovo. Případně první jedno (tři) písmena.

Velká písmena měsícuů a dnů
=VELKÁ2(HODNOTA.NA.TEXT(C13;"mmmm")) =VELKÁ2(ZLEVA(HODNOTA.NA.TEXT(C13;"mmmm");3)) =VELKÁ2(ZLEVA(HODNOTA.NA.TEXT(C13;"mmmm");1)) =VELKÁ2(HODNOTA.NA.TEXT(C13;"ddd")) =VELKÁ2(HODNOTA.NA.TEXT(C13;"dddd"))

Čas na desetiné číslo

Jak převést čas na desetinné číslo. Například 6:30 na 6,5. Například využít vzorce:

=CELÁ.ČÁST(A1)*24+HODINA(A1)+ZAOKROUHLIT(MINUTA(A1)/60;2)

Čas z desetiného čísla

Jak vytvořit čas z desetinného čísla. Například potřebujete převést 6,5 na 6:30. Využít lze vzorec:

' pokud mám čas 6,5 (jako 6 minut a 30 sekund) ' Minuty,Sekundy / 1440 =6,5/1440 ' pokud mám čas 6,5 (jako 6 hoddin a 30 minut) ' Hodiny,Minuty / 24 =6,5/24

Zaokrouhlovat čas (například čtvrt hodiny)

Jak zaokrouhlovat čas na čtvrthodiny, tak ať je zachován formát času:

  • 2:08 2:15
  • 3:20 3:15
=MROUND(A1;"0:15")

Funguje i když potřebujete zaokrouhlovat na 10 minut ;) atd.

=MROUND(A1;"0:10")

V kterých letech porobíhal projekt

Je zadán začátek a konec projektu (který trvá několik let) a je potřeba umět tento projekt filtrovat zda v daném roce probíhal.

Z času vypočíst mzdu - Excel
=KDYŽ(NEBO(ROK($C5)>E$4;ROK($D5)<E$4);0;1)

Soubor V kterých letech porobíhal projekt soubor ve formátu *.xls ke stažení zdarma.

Jak dlouho probíhal projekt?

Potřebujeme zjistit jak dlouho probíhal projekt, tj. kolik dnů, hodin a minut. V C1 je rozdíl mezi časem ukončení a časem začátku.

=CELÁ.ČÁST(C1) &" Dnů " & CELÁ.ČÁST(MOD(C1;CELÁ.ČÁST(C1))*24) & " hodin " & MINUTA(C1) & " Minut"

Průměrná rychlost

Znám ujetou vzdálenost v (km) buňka A1 a buňce B1 čas, který byl k překonání této vzdálenosti potřeba(hod:min). Řešení je jednoduché:

=A1/B1/24

Zjistit čtvrtletí

V buňce A1 je datum a potřebujeme zjisti, ve kterému čtvrtletí přísluší. Jedním z možných řešení je využít ROUNDUP:

=ROUNDUP(MĚSÍC(A1)/3;0)

nebo přes funkci VYHLEDAT

=VYHLEDAT(MĚSÍC(A1);{1;4;7;10};{1;2;3;4})

nebo s využitím CELÁ.ČÁST

=CELÁ.ČÁST((MĚSÍC(A1)-1)/3+1)

Java formát převod do Excel

Máte formát datmu typu 1079017149062 nebo 1420235100 a potřebujete získat datum v Excel formátu. pomoc je jednoduchá:

Pro verzi 1

=(A1/(3600*24*1000))+DATUM(1970;1;1)

Pro verzi 2

=(A1/(3600*24))+DATUM(1970;1;1)

Formát času nad 24 hodin

Je potřeba nastavit vlastní formát buněk na:

[h]:mm Formát času nad 24 hodin

Poznámka funguje i pro minuty

[mm]

Vlastní formát více

Více o vlastním formátování je popsáno v samostatném článku zde.

Ukázkové příklady - zdarma ke stažení

Soubor Datum a čas - praktické příklady Excel soubor ve formátu *.xlsx ke stažení zdarma.

Závěrem

Další příklady jsou v přípravě. Pokud Vás napadá nějaký zajímavý příklad, který lze řešit pomocí funkcí datum a čas můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře

" ; // echo "kompletni_cesta :" ; // echo $adresar_pro_vkladani ; // echo "
" ; // echo " Tady bude možnost přidání komentáře" ; // echo "
" ; // echo "
" ; $kompletni_cesta = "komentare"; $adresar_pro_vkladani = $kompletni_cesta; ?> \n"; reset($polozky); while (list($key, $val) = each($polozky)) { if ($val != "." && $val != "..") { if (!is_dir($val)) { // echo "$val
\n"; $fp = FOpen ($adresar_pro_vkladani."/".$val, "r"); //otevře soubor book.dat pro čtení $data = FRead ($fp, FileSize($adresar_pro_vkladani."/".$val)); //přečte data ze souboru a uloží do proměnné "data" - kvůli tomu aby se zobrazoval poslední příspěvek nahoře FClose($fp); echo "$data"; } } } ?>




Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2020 |