Vypočítat čas v Excelu (časový rozdíl, odpracované hodiny, sčítání/ odčítání)

Protože data a časy jsou v aplikaci Excel uloženy jako čísla na zadním konci, můžete snadno použít jednoduché aritmetické operace a vzorce pro hodnoty data a času.

Můžete například přidat dvě různé časové hodnoty nebo hodnoty data nebo můžete vypočítat časový rozdíl mezi dvěma danými daty/časy.

V tomto tutoriálu vám ukážu několik způsobů, jak provádět výpočty pomocí času v aplikaci Excel (například výpočet časového rozdílu, sčítání nebo odčítání času, zobrazování času v různých formátech a součet hodnot času)

Jak aplikace Excel zpracovává datum a čas?

Jak jsem již zmínil, data a časy jsou v buňce v Excelu uloženy jako čísla. Celé číslo představuje celý den a desetinná část čísla by představovala část dne (kterou lze převést na hodnoty hodin, minut a sekund)

Například hodnota 1 představuje 1. leden 1900 v aplikaci Excel, což je počáteční bod, od kterého aplikace Excel začíná brát v úvahu data.

Takže 2 by znamenalo 2. ledna 1990, 3 by znamenalo 3. ledna 1900 a tak dále a 44197 by znamenalo 1. ledna 2021.

Poznámka: Excel pro Windows a Excel pro Mac mají různá počáteční data. 1 v Excelu pro Windows by znamenalo 1. ledna 1900 a 1 v Excelu pro Mac by znamenalo 1. ledna 1904

Pokud jsou v těchto číslech nějaké číslice za desetinnou čárkou, Excel by je považoval za součást dne a lze je převést na hodiny, minuty a sekundy.

Například 44197,5 by znamenalo 1. ledna 2021 12:00:00 PM.

Pokud tedy pracujete s časovými hodnotami v Excelu, budete v podstatě pracovat s desetinnou částí čísla.

A Excel vám dává flexibilitu převést tuto desetinnou část do různých formátů, jako jsou pouze hodiny, pouze minuty, pouze sekundy nebo kombinace hodin, minut a sekund

Nyní, když chápete, jak je čas uložen v aplikaci Excel, podívejme se na několik příkladů, jak vypočítat časový rozdíl mezi dvěma různými daty nebo časy v aplikaci Excel

Vzorce pro výpočet časového rozdílu mezi dvěma časy

V mnoha případech vše, co chcete udělat, je zjistit celkový čas, který uplynul mezi dvěma časovými hodnotami (například v případě časového rozvrhu, který má In-time a Out-time).

Metoda, kterou zvolíte, bude záviset na tom, jak je v buňce uveden čas a v jakém formátu chcete výsledek.

Podívejme se na několik příkladů

Jednoduché odečítání výpočtu časového rozdílu v aplikaci Excel

Protože je čas v Excelu uložen jako číslo, najděte rozdíl mezi 2 časovými hodnotami, můžete snadno odečíst počáteční čas od koncového času.

Čas ukončení - čas začátku

Výsledkem odečtení by byla také desetinná hodnota, která by představovala čas, který uplynul mezi dvěma časovými hodnotami.

Níže je uveden příklad, kde mám počáteční a koncový čas a vypočítal jsem časový rozdíl jednoduchým odečtením.

Je možné, že se vaše výsledky zobrazí ve formátu času (místo desetinných míst nebo v hodinách/minutách). V našem výše uvedeném příkladu výsledek v buňce C2 ukazuje 09:30 dop. Místo 9,5.

To je naprosto v pořádku, protože Excel se pokouší zkopírovat formát ze sousedního sloupce.

Chcete -li to převést na desetinné číslo, změňte formát buněk na Obecné (možnost je na kartě Domů ve skupině Čísla)

Jakmile máte výsledek, můžete jej formátovat různými způsoby. Můžete například zobrazit hodnotu pouze v hodinách nebo minutách nebo v kombinaci hodin, minut a sekund.

Níže jsou uvedeny různé formáty, které můžete použít:

Formát	Co to dělá
h	Zobrazuje pouze hodiny, které uplynuly mezi dvěma daty
hh	Zobrazuje hodiny dvouciferným číslem (například 04 nebo 12)
hh: mm	Zobrazuje hodiny a minuty, které uplynuly mezi dvěma daty, například 10:20
hh: mm: ss	Zobrazuje hodiny, minuty a sekundy, které uplynuly mezi dvěma daty, například 10:20:36

A pokud vás zajímá, kde a jak použít tyto vlastní formáty data, postupujte podle následujících kroků:

1. Vyberte buňky, na které chcete použít formát data
2. Podržte klávesu Control a stiskněte klávesu 1 (nebo Command + 1, pokud používáte Mac)
3. V dialogovém okně Formát buněk, které se otevře, klikněte na kartu Číslo (pokud již není vybráno)
4. V levém podokně klikněte na Vlastní
5. Do pole Typ zadejte libovolný požadovaný formátový kód (v tomto případě používám hh: mm: ss)
6. Klikněte na OK

Výše uvedené kroky by změnily formátování a zobrazily by vám hodnotu podle formátu.

Všimněte si toho, že vlastní formátování čísel nemění hodnotu v buňce. Mění pouze způsob zobrazení hodnoty. Mohu se tedy rozhodnout, že v buňce budu zobrazovat pouze hodnotu hodiny, zatímco ta bude mít stále původní hodnotu.

Pro tip: Pokud celkový počet hodin přesáhne 24 hodin, použijte místo toho následující vlastní formát čísel: [hh]: mm: ss

Vypočítejte časový rozdíl v hodinách, minutách nebo sekundách

Když odečtete časové hodnoty, Excel vrátí desetinné číslo, které představuje výsledný časový rozdíl.

Protože každé celé číslo představuje jeden den, desetinná část čísla by představovala tu velkou část dne, kterou lze snadno převést na hodiny nebo minuty nebo sekundy.

Výpočet časového rozdílu v hodinách

Předpokládejme, že máte datovou sadu, jak je ukázáno níže, a chcete vypočítat počet hodin mezi dvěma časovými hodnotami

Níže je uveden vzorec, který vám poskytne časový rozdíl v hodinách:

= (B2-A2)*24

Výše uvedený vzorec vám poskytne celkový počet hodin, které uplynuly mezi dvěma časovými hodnotami.

Někdy se Excel snaží být nápomocný a výsledek vám poskytne také v časovém formátu (jak je uvedeno níže).

Můžete to snadno převést do formátu čísla kliknutím na kartu Domů a ve skupině Číslo zvolíte jako formát číslo.

Pokud chcete vrátit pouze celkový počet hodin uplynulých mezi těmito dvěma časy (bez desetinné části), použijte následující vzorec:

= INT ((B2-A2)*24)

Poznámka: Tento vzorec funguje pouze tehdy, jsou -li obě časové hodnoty stejného dne. Pokud se den změní (kde jedna z časových hodnot je jiného data a druhá jiného data), tento vzorec poskytne špatné výsledky. Podívejte se na část, kde pokrývám vzorec pro výpočet časového rozdílu, když se datum později změní v tomto kurzu.

Výpočet časového rozdílu v minutách

Chcete -li vypočítat časový rozdíl v minutách, musíte vynásobit výslednou hodnotu celkovým počtem minut za den (což je 1440 nebo 24*60).

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete vypočítat celkový počet minut, které uplynuly mezi počátečním a koncovým datem.

Níže je uveden vzorec, který to provede:

= (B2-A2)*24*60

Výpočet časového rozdílu v sekundách

Chcete -li vypočítat časový rozdíl v sekundách, musíte vynásobit výslednou hodnotu celkovým počtem sekund za den (což je nebo 24*60*60 nebo 86400).

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete vypočítat celkový počet sekund, které uplynuly mezi počátečním a koncovým datem.

Níže je uveden vzorec, který to provede:

= (B2-A2)*24*60*60

Výpočet časového rozdílu pomocí funkce TEXT

Další snadný způsob, jak rychle získat časový rozdíl bez starostí se změnou formátu, je použít funkci TEXT.

Funkce TEXT vám umožňuje zadat formát přímo ve vzorci.

= TEXT (koncové datum - počáteční datum, formát)

Prvním argumentem je výpočet, který chcete provést, a druhým argumentem je formát, ve kterém chcete zobrazit výsledek výpočtu.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete vypočítat časový rozdíl mezi těmito dvěma časy.

Zde je několik vzorců, které vám poskytnou výsledek v různých formátech

Zobrazit pouze počet hodin:

= TEXT (B2-A2, "hh")

Výše uvedený vzorec vám poskytne pouze výsledek, který ukazuje počet hodin, které uplynuly mezi dvojnásobnými hodnotami. Pokud je váš výsledek 9 hodin a 30 minut, stále se vám zobrazí pouze 9.

Ukažte celkový počet minut

= TEXT (B2-A2, "[mm]")

Ukažte celkový počet sekund

= TEXT (B2-A2, "[ss]")

Zobrazit hodiny a minuty

= TEXT (B2-A2, "[hh]: mm")

Zobrazit hodiny, minuty a sekundy

= TEXT (B2-A2, "hh: mm: ss")

Pokud vás zajímá, jaký je rozdíl mezi hh a [hh] ve formátu (nebo mm a [mm]), při použití hranatých závorek by vám poskytl celkový počet hodin mezi dvěma daty, i když hodinová hodnota je vyšší než 24. Pokud tedy odečtete dvě hodnoty data, kde je rozdíl větší než 24 hodin, pomocí [hh] získáte celkový počet hodin a hh pouze hodiny uplynulé v den koncového data .

Získejte časový rozdíl v jedné jednotce (hodiny/minuty) a ostatní ignorujte

Pokud chcete vypočítat časový rozdíl mezi těmito dvěma časovými hodnotami pouze v počtu hodin, minut nebo sekund, můžete použít vyhrazenou funkci HODINA, MINUTA nebo SEKUND.

Každá z těchto funkcí přebírá jeden jediný argument, kterým je časová hodnota, a vrací zadanou časovou jednotku.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete vypočítat celkový počet hodin minut a sekund, které mezi těmito dvěma časy uplynuly.

Níže jsou uvedeny vzorce, jak to provést:

Výpočet hodin uplynul mezi dvěma časy

= HODINA (B2-A2)

Výpočet minut z výsledku hodnoty času (kromě dokončených hodin)

= MINUTA (B2-A2)

Výpočet sekund z výsledku hodnoty času (kromě dokončených hodin a minut)

= DRUHÝ (B2-A2)

Několik věcí, které potřebujete vědět při práci s těmito vzorci HODINY, MINUTY a DRUHÉ:

• Rozdíl mezi časem ukončení a časem zahájení nemůže být záporný (což je často případ, kdy se datum změní). V takových případech by tyto vzorce vrátily #ČÍSLO! chyba
• Tyto vzorce používají pouze časovou část výsledné časové hodnoty (a denní část ignorují). Pokud je tedy rozdíl v koncovém čase a čase zahájení 2 dny, 10 hodin, 32 minut a 44 sekund, vzorec HOUR dá 10, vzorec MINUTE dá 32 a DRUHÝ vzorec dá 44

Vypočítat uplynulý čas do teď (od počátečního času)

Pokud chcete vypočítat celkový čas, který uplynul mezi počátečním časem a aktuálním časem, můžete místo koncového času použít vzorec TEĎ.

Funkce NOW vrací aktuální datum a čas v buňce, ve které je použita. Je to jedna z funkcí, která nevyžaduje žádný vstupní argument.

Pokud tedy chcete vypočítat celkový čas, který uplynul mezi počátečním časem a aktuálním časem, můžete použít následující vzorec:

= NYNÍ () - čas zahájení

Níže je uveden příklad, kde mám počáteční časy ve sloupci A a čas, který dosud uplynul ve sloupci B.

Pokud je časový rozdíl mezi počátečním datem a časem a aktuálním časem větší než 24 hodin, můžete výsledek naformátovat tak, aby zobrazoval den i časovou část.

To lze provést pomocí níže uvedeného vzorce TEXT:

= TEXT (NYNÍ ()-A2, "dd hh: ss: mm")

Totéž můžete dosáhnout také změnou vlastního formátování buňky (jak bylo popsáno dříve v tomto kurzu) tak, aby zobrazovala den i čas.

V případě, že váš počáteční čas má pouze časovou část, pak by to Excel považoval za čas 1. ledna 1990.

V tomto případě, pokud použijete funkci NYNÍ k výpočtu dosud uplynulého času, poskytne vám to špatný výsledek (protože výsledná hodnota by měla také celkový počet dní, které uplynuly od 1. ledna 1990).

V takovém případě můžete použít následující vzorec:

= NYNÍ ()- INT (NYNÍ ())- A2

Výše uvedený vzorec používá funkci INT k odebrání denní části z hodnoty vrácené funkcí now a ta se pak použije k výpočtu časového rozdílu.

Všimněte si, že TEĎ je volatilní funkce, která se aktualizuje vždy, když dojde ke změně v listu, ale neaktualizuje se v reálném čase

Vypočítat čas při změně data (vypočítat a zobrazit záporné časy v aplikaci Excel)

Dosud popsané metody fungují dobře, pokud je váš čas ukončení pozdější než čas zahájení.

Problém ale nastává, když je váš čas ukončení nižší než čas začátku. To se často stává, když vyplňujete pracovní výkazy, kde zadáváte pouze čas, nikoli celé datum a čas.

V takových případech, pokud pracujete na jednu noční směnu a změní se datum, existuje možnost, že váš čas ukončení bude dřívější než čas zahájení.

Pokud například začnete pracovat v 18:00 večer a dokončíte práci a časový limit v 9:00 ráno.

Pokud pracujete pouze s hodnotami času, odečtením času začátku od času konce získáte zápornou hodnotu 9 hodin (9 - 18).

A Excel nedokáže zvládnout záporné časové hodnoty (a pokud jde o to, ani lidé, pokud nemůžete cestovat časem)

V takových případech potřebujete způsob, jak zjistit, že se den změnil, a podle toho by měl být proveden výpočet.

Naštěstí na to existuje opravdu snadná oprava.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, kde mám počáteční a koncový čas.

Všimli byste si, že někdy je čas zahájení večer a konec čas ráno (což naznačuje, že se jednalo o noční směnu a den se změnil).

Pokud pro výpočet časového rozdílu použiji níže uvedený vzorec, ukáže mi to znaky hash v buňkách, kde je výsledkem záporná hodnota (na obrázku níže zvýrazněna žlutě).

= B2-A2

Zde je vzorec IF, který bez ohledu na to, zda je hodnota časového rozdílu záporná nebo ne, a v případě, že je záporný, vrátí správný výsledek

= IF ((B2-A2) <0,1- (A2-B2), (B2-A2))

I když to ve většině případů funguje dobře, v případě, že je počáteční čas a koncový čas vzdáleno více než 24 hodin, stále chybí. Někdo se například přihlásí v 9:00 hodin v den 1 a odhlásí v 11:00 v den 2.

Jelikož je to více než 24 hodin, neexistuje způsob, jak zjistit, zda se osoba odhlásila po 2 hodinách nebo po 26 hodinách.

Nejlepší způsob, jak to vyřešit, by bylo zajistit, aby položky zahrnovaly datum i čas, ale pokud je to jen čas, se kterým pracujete, výše uvedený vzorec by se měl postarat o většinu problémů ( vzhledem k tomu, že je nepravděpodobné, že by někdo pracoval déle než 24 hodin)

Sčítání/ odčítání času v Excelu

Doposud jsme viděli příklady, kdy jsme měli počáteční a koncový čas, a potřebovali jsme najít časový rozdíl.

Excel také umožňuje snadno přidat nebo odečíst pevnou časovou hodnotu od stávající hodnoty data a času.

Řekněme například, že máte seznam úkolů ve frontě, kde každý úkol trvá zadaný čas, a chcete vědět, kdy každý úkol skončí.

V takovém případě můžete snadno přidat čas, který bude každý úkol trvat, k času zahájení, abyste věděli, v jaké době se očekává dokončení úkolu.

Protože Excel ukládá hodnoty data a času jako čísla, musíte zajistit, aby čas, který se pokoušíte přidat, odpovídal formátu, který již Excel dodržuje.

Pokud například přidáte 1 k datu v aplikaci Excel, získáte další datum. Důvodem je, že 1 představuje celý den v aplikaci Excel (což se rovná 24 hodinám).

Pokud tedy chcete přidat 1 hodinu k existující časové hodnotě, nemůžete pokračovat a jednoduše k ní přidat 1. musíte se ujistit, že tuto hodinu převedete na desetinnou část, která představuje jednu hodinu. a to samé platí pro přidání minut a sekund.

Použití funkce ČAS

Funkce času v aplikaci Excel převezme hodnotu hodiny, minutu a sekundy a převede ji na desetinné číslo, které představuje tento čas.

Pokud například chci přidat 4 hodiny ke stávajícímu času, mohu použít následující vzorec:

= Čas začátku + ČAS (4,0,0)

To je užitečné, pokud víte, kolik hodin, minut a sekund chcete přidat ke stávajícímu času, a jednoduše použijete funkci TIME, aniž byste se museli starat o správné převedení času na desítkovou hodnotu.

Všimněte si také, že funkce TIME bude brát v úvahu pouze celočíselnou část zadané hodnoty hodiny, minuty a sekundy. Pokud například použiji 5,5 hodiny ve funkci TIME, přidá pouze pět hodin a desítkovou část ignoruje.

Všimněte si také, že funkce TIME může přidávat pouze hodnoty, které jsou kratší než 24 hodin. Pokud je hodnota vaší hodiny větší než 24, způsobí to nesprávný výsledek.

A totéž platí pro minuty a druhou část, kde funkce bude brát v úvahu pouze hodnoty, které jsou menší než 60 minut a 60 sekund

Stejně jako jsem přidal čas pomocí funkce TIME, můžete také odečíst čas. Ve výše uvedených vzorcích stačí změnit znaménko + na záporné znaménko

Použití základní aritmetiky

Když se funkce času snadno a pohodlně používá, přichází s několika omezeními (jak je uvedeno výše).

Pokud chcete větší kontrolu, můžete použít aritmetickou metodu, kterou zde uvedu.

Princip je jednoduchý - převeďte časovou hodnotu na desítkovou hodnotu, která představuje část dne, a poté ji můžete přidat k libovolné časové hodnotě v Excelu.

Pokud například chcete k existující časové hodnotě přidat 24 hodin, můžete použít následující vzorec:

= Start_time + 24/24

To jen znamená, že ke stávající časové hodnotě přidávám jeden den.

Když vezmeme stejný koncept kupředu, řekněme, že chcete k časové hodnotě přidat 30 hodin, můžete použít následující vzorec:

= Start_time + 30/24

Výše uvedený vzorec dělá totéž, kde celočíselná část (30/24) by představovala celkový počet dní v čase, který chcete přidat, a desetinná část by představovala hodiny/minuty/sekundy

Podobně pokud máte určitý počet minut, které chcete přidat k časové hodnotě, můžete použít následující vzorec:

= Start_time + (minuty k přidání)/24*60

A pokud máte počet sekund, které chcete přidat, můžete použít následující vzorec:

= Start_time + (minuty k přidání)/24*60*60

I když tato metoda není tak snadná jako používání funkce času, připadá mi to mnohem lepší, protože funguje ve všech situacích a dodržuje stejný koncept. na rozdíl od časové funkce si nemusíte dělat starosti s tím, zda čas, který chcete přidat, je kratší než 24 hodin nebo delší než 24 hodin

Stejný koncept můžete sledovat i při odečítání času. Stačí ve výše uvedených vzorcích změnit znaménko + na záporné znaménko

Jak SUMMAT čas v Excelu

Někdy můžete chtít rychle sečíst všechny časové hodnoty v Excelu. Přidání více časových hodnot do Excelu je celkem jednoduché (stačí jednoduchý vzorec SUMA)

Existuje však několik věcí, které potřebujete vědět, když přidáte čas v aplikaci Excel, konkrétně formát buňky, který vám ukáže výsledek.

Podívejme se na příklad.

Níže mám seznam úkolů spolu s časem, který každý úkol zabere ve sloupci B, a chci tyto časy rychle přidat a vědět, jaký bude celkový čas všech těchto úkolů.

V buňce B9 jsem použil jednoduchý vzorec SUM k výpočtu celkového času, který budou všechny tyto úkoly trvat, a dává mi hodnotu 18:30 (což znamená, že dokončení bude trvat 18 hodin a 20 minut) všechny tyto úkoly)

Zatím vše dobré!

Jak součet přes 24 hodin v aplikaci Excel

Nyní se podívejte, co se stane, když se změním v době, kdy bude úkol 2 trvat 1 hodinu až 10 hodin.

Výsledek nyní říká 03:20, což znamená, že dokončení všech těchto úkolů by mělo trvat 3 hodiny a 20 minut.

To je nesprávné (samozřejmě)

Problémem zde není nepořádek v Excelu. Problém je v tom, že buňka je naformátována tak, že vám ukáže pouze časovou část výsledné hodnoty.

A protože výsledná hodnota zde je více než 24 hodin, Excel se rozhodl převést 24hodinovou část na den, odebrat ji z hodnoty, která se zobrazuje uživateli, a zobrazit pouze zbývající hodiny, minuty a sekundy.

Naštěstí to lze snadno opravit.

Vše, co musíte udělat, je změnit formát buňky, aby se zobrazovaly hodiny, i když překročí 24 hodin.

Níže jsou uvedeny některé formáty, které můžete použít:

Formát	Očekávaný výsledek
[h]: mm	28:30
[m]: ss	1710:00
d „D“ hh: mm	1 D 04:30
d „D“ hh „Min“ ss „Sec“	1 D 04 Min 00 Sek
d „Den“ hh „Minuta“ ss „Sekundy“	1 den 04 minuta 00 sekund

Formát můžete změnit tak, že přejdete do dialogového okna buněk formátu a použijete vlastní formát, nebo použijete funkci TEXT a použijete kterýkoli z výše uvedených formátů v samotném vzorci

Níže uvedený vzorec TEXT můžete použít k zobrazení času, i když je více než 24 hodin:

= TEXT (SUM (B2: B7), "[h]: mm: ss")

nebo níže uvedený vzorec, pokud chcete převést hodiny přesahující 24 hodin na dny:

= TEXT (SUM (B2: B7), "dd hh: mm: ss")

Výsledky zobrazující hash (###) namísto data/času (důvody + oprava)

V některých případech můžete zjistit, že místo zobrazení časové hodnoty aplikace Excel zobrazuje v buňce symboly hash.

Zde je několik možných důvodů a způsoby, jak je opravit:

Sloupek není dostatečně široký

Pokud buňka nemá dostatek místa k zobrazení úplného data, může zobrazit symboly hash.

Má snadnou opravu - změňte šířku sloupce a zvětšete jej.

Záporná hodnota data

Hodnota data nebo času nemůže být v Excelu záporná. v případě, že vypočítáváte časový rozdíl a ukáže se, že je záporný, Excel vám ukáže hashovací symboly.

Způsob oprav, jak změnit vzorec, abyste získali správný výsledek. Pokud například vypočítáváte časový rozdíl mezi těmito dvěma časy a mění se datum, musíte upravit vzorec, aby to odpovídal.

V ostatních případech můžete pomocí funkce ABS převést zápornou časovou hodnotu na kladné číslo, aby se zobrazila správně. Alternativně můžete také vzorec IF zkontrolovat, zda je výsledkem záporná hodnota, a vrátit něco smysluplnějšího.

V tomto tutoriálu jsem se zabýval tématy o výpočtu času v aplikaci Excel (kde můžete vypočítat časový rozdíl, přidat nebo odečíst čas, zobrazit čas v různých formátech a součet hodnot času)

Doufám, že jste našli tento návod užitečný.