Formátování časových údajů a vzájemný převod mezi zobrazením času desetinným číslem a tradičním zobrazením hodiny:minuty:vteřiny bývá někdy problematické.
V přiloženém souboru jsou některé, ne zcela obecně známé skutečnosti ve zpracování časových údajů Excelem, vysvětleny.
Obracím se na Vás s prosbou o radu s převodem, nebo formátováním čísla v Excelu jako čas. Jde o to, že potřebuji vygenerovat geometrickou řadu čísel, která je tvořena vypočítanými hodnotami.
Tyto hodnoty vyjadřují počet sekund s přesností na 2 desetinná místa např. 7298,24 Pokud to zjednoduším do jednoho vzorce a na konkrétní čísla, jedná se v příkladu o výsledek vzorce „=(ZAOKROUHLIT((6501,994709*POWER(2;1/6));2))“
Tento výsledek potřebuji v jiném sloupci zobrazit ve formátu: „02:01:38:24“ kde 02 jsou hodiny, 01 minuty 38 vteřiny a 24 setiny vteřin.
Vyřešil jsem to sice výpočtem, sloučením výsledků a doplněním nul. Dostanu se sice ke správnému výsledku, ale za cenu vzorce na půl stránky. Neexistuje nějaké jednoduché a přehledné řešení?
V tomto případě postup poměrně jednoduchý – spočívá ve vytvoření vhodného formátu pro zobrazení čísla reprezentujícího časový údaj.
Postup:
- vstupní hodnotu převedeme na část 24h cyklu pomocí vzorce
7298,24 / (246060)=0,08447037 - výslednou hodnotu zobrazíme pomocí vhodného formátu
Excel není standardně vybaven formátem pro zobrazení setin vteřiny – musíme jej vytvořit.
v dialogovém okně Formát buněk zapíšeme pro druh Vlastní typ formátu h:mm:ss,00
Principy zobrazení časových a datumových údajů v Excelu
Pravidlo č.1
Časové údaje jsou v Excelu interpretovány jako desetinná čísla z intervalu <0;1), představující příslušnou část dne.
Nápověda Excelu říká:
(Pokud chceme znát přesnou hodnotu pravé strany intervalu, pak platí, že desetinné číslo je hodnota v rozmezí od 0 (nula) do 0,99999999, která představuje čas od 0:00:00 (12:00:00 dop.) do 23:59:59 (11:59:59 odp.).)
Není to však zcela pravda, jak můžeme vidět na následujících údajích
číslo 0,99999994 je interpretováno jako čas 23:59:59,99 a číslo 0,99999995 již představuje začátek nového 24h cyklu 0:00:00,00.
Číslo 0 představuje časový údaj, kdy pomyslné hodiny překročí půlnoc a začíná nový 24 hodinový cyklus – tedy čas, který by například na informační tabuli v letištní hale byl označen jako 0:00.
Excel nemá připravený standardní formát pro zobrazení setin sekundy, můžeme jej však vytvořit v dialogovém okně Formát buněk (karta Domů > aktivační tlačítko) nebo Ctrl+1 (případně Ctrl+Shift+1)) zápisem typu vlastní s maskou h:mm:ss,00.
Příklady:
Číslo 0,25 představuje čas odpovídající 1/4 dne, tedy 6:00.
Číslo 0,5 představuje čas odpovídající 1/2 dne, tedy 12:00.
Číslo 1 by v tomto pojetí mělo představovat ukončený cyklus 24hodin (půlnoc), tedy čas 24:00. Protože by ale došlo k nepřijatelné situaci, kdy by pro jeden časový údaj existovala duplicitní číselná reprezentace (24:00 a 0:00, hodnoty 1 a 0), číslo 1 již není považováno za časový údaj, ale za datumový – hodnota 1 představuje kalendářní datum 1.1.1900.
Pravidlo č.2
Kalendářní údaje (datum) jsou v Excelu interpretovány jako celé číslo z intervalu <1;99999>), představující konkrétní datum.
Excel ukládá kalendářní data jako pořadová čísla, takže je lze použít při výpočtech. 1. leden roku 1900 ve výchozím nastavení představuje pořadové číslo 1 a 1. leden 2008 pořadové číslo 39448, protože se jedná o 39 448. den po 1. lednu roku 1900
Příklady:
Číslo 1 představuje datum odpovídající dni 1.1.1900.
Číslo 30000 představuje datum odpovídající dnu, kdy uplynulo 30000dní od fiktivního dne D 0.1.1900 – tedy datum 18.2.1982.
Datum 24.12.2008 Excel interpretuje jako číslo odpovídající počtu dní, které uplynuly od fiktivního dne D 0.1.1900 – tedy 39806.
