V dnešním videu se podíváme na 5 frustrujících problémů, se kterými se můžete setkat, když v Excelu počítáte s časem. Ukážeme si, jak vyřešit problémy s formátem času, jak vyřešit nesprávné výpočty, když počítáte rozdíl v čase přes půlnoc a nebo si ukážeme, jak vyřešit, že vám součet časů ukazuje nesprávné výsledky.
Excelový soubor ke stažení
5 frustrujících problémů s časem v Excelu
A vrhneme se rovnou na první problém.
Nesprávný formát
Čas je v Excelu uchováván stejně jako datum ve formě čísel. Zatímco datum je uchováván jako celé číslo, tak čas je uložený jako desetinné číslo, jelikož se jedná o část dne. Více o tom, jak čas v Excelu funguje a jak s ním pracovat, najdete v našem předchozím videu Čas v Excelu. Ve videu pro členy Akademie Excelu máme i video Praktické příklady pro počítání s časem v Excelu.
Prvním problémem, na který můžete ve spojitosti s časem v Excelu narazit je formát. Na formátu u času totiž záleží. Pokud se vám čas zobrazuje v podobě desetinného čísla, tak to znamená, že na buňce máte uplatněný obecný formát. Aby se vám čas zobrazil správně musíte na buňku kliknout a změnit formát buňky pomocí pravého tlačítka myši, Formát buňky a v nabídce vybrat Čas a vybrat si jeden z přednastavených formátů. Po potvrzení se čas zobrazí ve správném formátu.
Čas můžete v Excelu psát i pomocí anglických zkratek AM pro dopolední časy a PM pro odpolední časy. V takovém případě si ale musíte dát pozor na to, že mezi časem a zkratkou musí být vždy mezera. Pokud na mezeru zapomenete, tak se bude čas tvářit jako textová hodnota, což poznáte i podle zarovnání hodnoty v buňce. V buňce jsme zapomněli na mezeru, takže je hodnota zarovnána v buňce doleva, což značí text. Když mezi čase a zkratku dáme mezeru, tak se hodnota změní na čas a hodnota se zarovná doprava.
Čas přes půlnoc
Dalším frustrujícím problémem v Excelu je, když počítáte rozdíl v čase, který přesahuje jeden den. V tabulce máme několik příkladů, kde máme počáteční a konečný čas. Chceme spočítat rozdíl mezi časy. K tomu nám stačí jednoduchá kalkulace v podobě rozdílu. Klikneme do buňky a napíšeme rovná se a odečteme od sebe konečný a počáteční čas. Vzorec potvrdíme a stáhneme ho dolů. U prvních tří výpočtů vzorec funguje a vrací nám správný rozdíl mezi časem. U posledních tří výpočtů se ovšem vrací chybová hláška. Když klikneme na první chybovou hlášku a v příkazovém řádku ji označíme a podíváme se na výsledek vzorce pomocí klávesy F9, tak se zobrazí záporné číslo, které Excel neumí přeložit jako čas. Vrátíme se zpět do zobrazení vzorce pomocí kláves CTRL+Z. Důvod, proč se čas vrátil jako záporné číslo je ten, že počítáme rozdíl v čase, který překračuje půlnoc. Problém můžete vyřešit dvojím způsobem, buď použijete funkci KDYŽ nebo funkci MOD.
Nejprve vyřešíme příklad pomocí podmínkové funkce KDYŽ, anglicky funkce IF. Klikneme do buňky a napíšeme funkci KDYŽ, kde ověříme podmínku, že koncový čas je vyšší než počáteční čas. Pokud je tato podmínka splněná, tak to znamená, že výpočet nejde přes půlnoc a v takovém případě chceme vrátit koncový čas. A pokud podmínka splněná nebude, konečný čas bude menší než počáteční čas, tak to znamená, že výpočet jde přes půlnoc, tak v takovém případě chceme ke konečnému času přičíst jedničku. Ukončíme závorku u funkce KDYŽ a od funkce když teď odečteme počáteční čas. To je celá funkce. Funkci ukončíme a stáhneme ji dolů a teď funkce správně spočítala rozdíl v čase pro všechny řádky.
Druhou možností je použít funkci MOD. V takovém případě bychom napsali funkci MOD, ve které bychom odečetli koncový čas od počátečního času a jako dělitel vybereme jedničku, jelikož ve skutečnosti hodnoty nechceme dělit. Funkci ukončíme a potvrdíme a stáhneme ji dolů. I tato možnost vrací ve všech případech správný rozdíl v čase.
Součet časů
Na další problém u času můžete narazit, když chcete čas sčítat. V tabulce máme tabulku výkazu za jeden týden, kam si pracovník zapisuje počet odpracovaných hodin. Máme zde vždy počáteční čas, kdy začal pracovat a čas, kdy z práce odešel. Úkolem je spočítat, kolik hodin pracovník odpracoval za týden. Rozdíl v čase zjistíme tak, že od sebe odečteme koncový a počáteční čas. Jelikož žádný čas nepřekračuje půlnoc, tak můžeme použít prostý odečet. Odečteme od sebe hodnoty a stáhneme je pro všechny řádky dolů. Teď musíme časy sečíst. Klikneme do buňky celkového součtu a použijeme funkci SUMA. Klikneme do buňky a použijeme klávesovou zkratku ALT+=, čím se do buňky vloží SUMA. Po potvrzení je na první pohled jasné, že výpočet není správně. Chyba není ve funkci SUMA, ale ve formátu buňky.
Pokud je celkový součet hodin vyšší než 24, tak musíme použít speciální formát času. Označíme celkový součet a pomocí pravého tlačítka myši vybereme Formát buněk a vybereme Vlastní formát. Aby čas správně počítal, tak ve vlastním formátu musíme vybrat formát, který má písmeno h, jako hodiny, uvedený v hranatých závorkách. To je formát, který zajistí, že budou hodiny správně sečtené, i když součet překročí hodnotu 24.
Čas přes 31 dnů
V dalším příkladu máme tabulku, kde máme uvedený čas v hodinách, minutách a sekundách. Může se jednat například o celkový počet hodiny, které nám zabraly práce na projektech. Jak vidíme, tak někde hodiny přesahují 24 hodin, takže dokončení projektů zabralo více dnů. Nechceme mít ovšem časy vyjádřené ve formátu hodin, ale raději bychom viděli rozpad času po dnech, hodinách a minutách. To můžeme jednoduše udělat pomocí vlastního formátu. Nicméně narazíme zde na jeden problém. Klikneme do buňky a odkážeme se na původní čas. Potvrdíme a stáhneme čas dolů pro všechny řádky. Teď tento sloupec označíme a vybereme Formát buněk a Vlastní formát. Pro zobrazení dnů, hodin, minut a vteřin použijeme formát v následující podobě d”d” h”h” m”m” s”s”. V nápovědě vidíme, že se tím formát času mění na formát, který potřebujeme. Když máme formát hotový, tak potvrdíme.
První čas se z 36 hodin změnil na 1 den a 12 hodin a 25 minut. Druhý projekt trval 2 dny a 25 minut. Třetí projekt trval jen 12 hodin atd. Podívejte se ovšem na poslední projekt, který trval 768 hodin. U něho se nám zobrazil celkový čas trvání 1 den, což evidentně není správně. Důvodem je to, že Excel v této podobě umí počítat jen do 31 dnů. Jakmile výpočet přesáhne 31 dnů, tak se vynuluje a začne počítat zase od začátku. Když si vedle pomůžeme pomocným výpočtem, tak zjistíme, že 31 dnů po 24 hodinách je 744 plus 24 hodin, který má poslední den je 768 hodin, což je půlnoc dalšího dne, takže proto výpočet vrací 1 den, jelikož jsme přesáhli půlnoc u 31. dne. V této podobě tedy můžete počítat jen pokud celkové hodiny nepřesáhnou 31 dnů.
Výpočty s časem
Největším chytákem u práce s časem v Excelu je vždy formát. Na ten si musíte dávat pozor i tehdy, když chcete s časem provádět základní výpočty. V dalším příkladu máme tabulku s docházkou a máme nejprve spočítat počet odpracovaných hodin. To uděláme jednoduše tak, že od sebe odečteme konečný a počáteční čas. Žádný čas nepřekračuje půlnoc, takže časy můžeme jednoduše odečíst. Výsledkem je počet odpracovaných hodin. Co když teď ale potřebujeme od rozdílu odečíst 30 minut, které má pracovník na přestávku? Pokud by nás napadlo od sebe odečíst rozdíl a 30 minut, tak dostaneme nesprávný výsledek nebo chybovou hlášku. Proč? U výpočtu rozdílu odečet fungoval. Důvodem je rozdílný formát jednotlivých buněk. Když označíme buňku s rozdílem tak zjistíme, že rozdíl je zobrazený jako desetinné číslo. A teď od něho odečítáme hodnotu 30, čímž se dostaneme do mínusu. Výsledkem je tedy záporné číslo, což Excel neumí přeložit jako čas.
Odečet u koncového a počátečního datumu fungoval, jelikož obě buňky měli shodný formát. Oba časy byly uchované jako desetinná čísla a proto výpočet fungoval. Zobrazení je následně upraveno jen formátem buňky. U tohoto druhého výpočtu kombinujeme desetinné číslo a celé číslo, takže výpočet nefunguje. Abychom dosáhli správného výsledku, tak musíme sladit formáty.
Napíšeme rovná se a označíme buňku s celkovým odpracovaným časem. Víme, že toto číslo se zobrazí jako desetinné číslo, takže i 30 minut musíme převést na správné časové vyjádření, a to pomocí funkce ČAS, anglicky funkce TIME. Přestávku odčítáme, takže mínus a funkce ČAS, kde hodina je nula, minuty jsou 30 minut a sekundy jsou nula. Ukončíme závorku a funkci potvrdíme. Teď se vrátil správně celkový odpracovaný čas bez přestávky. Abychom mohli funkci poslat dolů, tak ovšem musíme zafixovat hodnotu minut ve funkci ČAS. Vrátíme se k funkci a buňku zafixujeme. Teď ji můžeme poslat dolů.
Jedna odpověď
😊👍