Výukový program Excel: Výpočet data a času (vypočítat rozdíl, věk, přidat/odečíst)

V Excelu se často používá výpočet data a času, jako je výpočet rozdílu mezi dvěma daty/časy, přidání nebo odečtení data a času, získání věku na základě data porodu a tak dále. Zde v tomto tutoriálu uvádí téměř scénáře výpočtu data a času a poskytuje vám související metody.

V tomto tutoriálu vytvořím několik příkladů pro vysvětlení metod, můžete změnit odkazy podle potřeby, když použijete níže uvedený kód nebo vzorce VBA

1. Vypočítejte rozdíl mezi dvěma daty/časy

Výpočet rozdílu mezi dvěma daty nebo dvěma časy může být nejběžnějším problémem výpočtu data a času, se kterým se při každodenní práci s Excelem setkáte. Níže uvedené příklady vám mohou pomoci zvýšit efektivitu, když narazíte na stejné problémy.

1.11 Vypočítejte rozdíl mezi dvěma daty ve dnech/měsících/rokech/týdnech

Funkci Excel DATEDIF lze použít k rychlému výpočtu rozdílu mezi dvěma daty ve dnech, měsících, letech a týdnech.

Kliknutím zobrazíte další podrobnosti o DATEDIF funkce

Rozdíl dnů mezi dvěma daty

Chcete-li získat rozdíl dnů mezi dvěma daty v buňce A2 a B2, použijte vzorec jako tento

=DATEDIF(A2,B2,"d")

Stisknout vstoupit klíč k získání výsledku.

Rozdíl měsíců mezi dvěma daty

Chcete-li získat měsíční rozdíl mezi dvěma daty v buňce A5 a B5, použijte vzorec jako tento

=DATEDIF(A5,B5,"m")

Stisknout vstoupit klíč k získání výsledku.

Rozdíl let mezi dvěma daty

Chcete-li získat letový rozdíl mezi dvěma daty v buňce A8 a B8, použijte vzorec jako tento

=DATEDIF(A8,B8,"y")

Stisknout vstoupit klíč k získání výsledku.

Rozdíl týdnů mezi dvěma daty

Chcete-li získat týdenní rozdíl mezi dvěma daty v buňce A11 a B11, použijte vzorec jako tento

=DATEDIF(A11,B11,"d")/7

Stisknout vstoupit klíč k získání výsledku.

Poznámka:

1) Když použijete výše uvedený vzorec k získání rozdílu týdnů, může vrátit výsledek ve formátu data, musíte výsledek naformátovat na obecný nebo číslo, jak potřebujete.

2) Když použijete výše uvedený vzorec k získání rozdílu týdnů, může se vrátit na desetinné číslo, pokud chcete získat celé číslo týdne, můžete přidat funkci ROUNDDOWN, jak je uvedeno níže, abyste získali celočíselný rozdíl týdnů:

=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)

1.12 Počítat měsíce ignorovat roky a dny mezi dvěma daty

Pokud chcete vypočítat rozdíl měsíců bez ohledu na roky a dny mezi dvěma daty, jak ukazuje níže uvedený snímek obrazovky, zde je vzorec, který vám může pomoci.

=DATEDIF(A2,B2,"ym")

Stisknout vstoupit klíč k získání výsledku.

A2 je počáteční datum a B2 je koncové datum.

1.13 Počítat dny ignorovat roky a měsíce mezi dvěma daty

Pokud si chcete pouze vypočítat rozdíl dnů bez ohledu na roky a měsíce mezi dvěma daty, jak je uvedeno níže, zde je vzorec, který vám může pomoci.

=DATEDIF(A5,B5,"md")

Stisknout vstoupit klíč k získání výsledku.

A5 je počáteční datum a B5 je koncové datum.

1.14 Vypočítejte rozdíl mezi dvěma daty a návratovými roky, měsíci a dny

Pokud chcete získat rozdíl mezi dvěma daty a vrátit xx let, xx měsíců a xx dní, jak ukazuje níže uvedený snímek obrazovky, je zde také poskytnut vzorec.

=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"

Stisknout vstoupit klíč k získání výsledku.

A8 je počáteční datum a B8 je koncové datum.

1.15 Vypočítejte rozdíl mezi datem a dneškem

Chcete-li automaticky vypočítat rozdíl mezi datem a dneškem, stačí změnit koncové_datum ve výše uvedených vzorcích na TODAY(). Zde slouží k výpočtu rozdílu dnů mezi minulým datem a dneškem jako příklad.

=DATEDIF(A11,TODAY(),"d")

Stisknout vstoupit klíč k získání výsledku.

Pozor: pokud chcete vypočítat rozdíl mezi budoucím datem a dneškem, změňte počáteční_datum na dnešek a budoucí datum vezměte jako koncové_datum takto:

=DATEDIF(TODAY(),A14,"d")

Všimněte si, že počáteční_datum musí být menší než koncové_datum ve funkci DATEDIF, jinak se vrátí na #NUM! chybová hodnota.

1.16 Vypočítat pracovní dny s dovolenou nebo bez dovolené mezi dvěma daty

Někdy může být potřeba spočítat počet pracovních dnů s svátky nebo bez nich mezi dvěma danými daty.

V této části použijete funkci NETWORKDAYS.INTL:

klikněte NETWORKDAYS.INTL znát jeho argumenty a použití.

Počítejte pracovní dny se svátky

Chcete-li počítat pracovní dny se svátky mezi dvěma daty v buňce A2 a B2, použijte prosím následující vzorec:

=NETWORKDAYS.INTL(A2,B2)

Stisknout vstoupit klíč k získání výsledku.

Počítejte pracovní dny bez svátků

Chcete-li počítat pracovní dny se svátky mezi dvěma daty v buňce A2 a B2 a s výjimkou svátků v rozsahu D5:D9, použijte prosím tento vzorec:

=NETWORKDAYS.INTL(A5;B5,1;5;D9:DXNUMX)

Stisknout vstoupit klíč k získání výsledku.

Poznámka:

Ve výše uvedených vzorcích berou sobotu a neděli jako víkend, pokud máte různé víkendové dny, změňte prosím argument [víkend], jak potřebujete.

1.17 Vypočítejte víkendy mezi dvěma daty

Pokud chcete počítat počet víkendů mezi dvěma daty, mohou vám pomoci funkce SUMPRODUCT nebo SUM.

Chcete-li počítat víkendy (sobota a neděle) mezi dvěma daty v buňce A12 a B12:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))

Or

=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))

Stisknout vstoupit klíč k získání výsledku.

1.18 Vypočítat konkrétní den v týdnu mezi dvěma daty

Pro počítání konkrétního dne v týdnu, jako je pondělí, mezi dvěma daty vám může pomoci kombinace funkcí INT a WEEKDAY.

Buňka A15 a B15 jsou dvě data, mezi která chcete počítat pondělí, použijte prosím vzorec takto:

=INT((WEEKDAY(A15- 2)-A15 +B15)/7)

Stisknout vstoupit klíč k získání výsledku.

Změňte číslo dne v týdnu ve funkci WEEKDAY pro počítání jiného dne v týdnu:

1 je neděle, 2 je pondělí, 3 je úterý, 4 je středa, 5 je čtvrtek, 6 je pátek a 7 je sobota)

1.19 Vypočítat zbývající dny v měsíci/roku

Někdy možná budete chtít znát zbývající dny v měsíci nebo roce na základě dodaného data, jak ukazuje níže uvedený snímek obrazovky:

Získejte zbývající dny v aktuálním měsíci

klikněte EOMĚSÍC znát argument a použití.

Chcete-li získat zbývající dny aktuálního měsíce v buňce A2, použijte následující vzorec:

=EOMONTH(A2,0)-A2

Stisknout vstoupit a přetažením úchytu automatického vyplňování tento vzorec v případě potřeby aplikujte na další buňky.

Tip: výsledky mohou být zobrazeny jako formát data, stačí je změnit na obecný nebo číselný formát.

Získejte zbývající dny v aktuálním roce

Chcete-li získat zbývající dny aktuálního roku v buňce A2, použijte následující vzorec:

=DATE(YEAR(A2),12,31)-A2

Stisknout vstoupit a přetažením úchytu automatického vyplňování tento vzorec v případě potřeby aplikujte na další buňky.

1.21 Vypočítejte rozdíl mezi dvěma časy

Abyste získali rozdíl mezi dvěma časy, zde jsou dva jednoduché vzorce, které vám mohou pomoci.

Předpokládejme, že v buňce A2 a B2 obsahují samostatně start_time a end_time pomocí vzorců jako:

=B2-A2

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

Stisknout vstoupit klíč k získání výsledku.

Poznámka:

• Pokud použijete end_time-start_time, můžete výsledek naformátovat do jiného formátu času, jak potřebujete, v dialogovém okně Formát buněk.
• Pokud použijete TEXT(end_time-first_time,"time_format"), zadejte formát času, ve kterém se má výsledek zobrazit ve vzorci, například TEXT(end_time-first_time,"h") vrátí 16.
• Pokud je end_time menší než start_time, oba vzorce vrátí chybové hodnoty. Chcete-li tento problém vyřešit, můžete na začátek těchto vzorců přidat ABS, například ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss"), a poté naformátovat výsledek jako čas.

1.22 Vypočítejte rozdíl mezi dvěma časy v hodinách/minutách/sekundách

Pokud chcete vypočítat rozdíl mezi dvěma časy v hodinách, minutách nebo sekundách, jak ukazuje níže uvedený snímek obrazovky, postupujte podle této části.

Získejte hodinový rozdíl mezi dvěma časy

Chcete-li získat hodinový rozdíl mezi dvěma časy v A5 a B5, použijte prosím vzorec takto:

=INT((B5-A5)*24)

Stisknout vstoupit poté naformátujte výsledek formátu času jako obecný nebo číselný.

Pokud chcete získat rozdíl v desetinných hodinách, použijte (end_time-start_time)*24.

Získejte minutový rozdíl mezi dvěma časy

Chcete-li získat rozdíl minut mezi dvěma časy v A8 a B8, použijte prosím vzorec takto:

=INT((B8-A8)*1440)

Stisknout vstoupit poté naformátujte výsledek formátu času jako obecný nebo číselný.

Pokud chcete získat rozdíl v desetinných minutách, použijte (end_time-start_time)*1440.

Získejte sekundový rozdíl mezi dvěma časy

Chcete-li získat sekundový rozdíl mezi dvěma časy v A5 a B5, použijte prosím vzorec takto:

=(B11-A11)*86400)

Stisknout vstoupit poté naformátujte výsledek formátu času jako obecný nebo číselný.

1.23 Vypočítat hodinový rozdíl pouze mezi dvěma časy (ne přesáhnout 24 hodin)

Pokud rozdíl mezi dvěma časy nepřesáhne 24 hodin, může funkce HOUR rychle získat hodinový rozdíl mezi těmito dvěma časy.

klikněte HODINA pro více podrobností o této funkci.

Chcete-li získat hodinový rozdíl mezi časy v buňce A14 a B14, použijte funkci HOUR takto:

=HOUR(B14-A14)

Stisknout vstoupit klíč k získání výsledku.

Start_time musí být menší než end_time, jinak vzorec vrátí #NUM! chybová hodnota.

1.24 Vypočítat minutový rozdíl pouze mezi dvěma časy (nepřekročit 60 minut)

Funkce MINUTA dokáže rychle zjistit rozdíl mezi těmito dvěma časy pouze v minutách a ignorovat hodiny a sekundy.

klikněte MINUTE pro více podrobností o této funkci.

Chcete-li získat pouze minutový rozdíl mezi časy v buňce A17 a B17, použijte funkci MINUTE takto:

=MINUTE(B17-A17)

Stisknout vstoupit klíč k získání výsledku.

Start_time musí být menší než end_time, jinak vzorec vrátí #NUM! chybová hodnota.

1.25 Vypočítat sekundový rozdíl pouze mezi dvěma časy (nepřekročit 60 sekund)

Funkce SECOND dokáže rychle zjistit rozdíl mezi těmito dvěma časy a ignorovat hodiny a minuty.

klikněte DRUHÝ pro více podrobností o této funkci.

Chcete-li získat pouze sekundový rozdíl mezi časy v buňce A20 a B20, použijte funkci SECOND takto:

=SECOND(B20-A20)

Stisknout vstoupit klíč k získání výsledku.

Start_time musí být menší než end_time, jinak vzorec vrátí #NUM! chybová hodnota.

1.26 Vypočítejte rozdíl mezi dvěma časy a návratovými hodinami, minutami, sekundami

Pokud chcete zobrazit rozdíl mezi dvěma časy jako xx hodin xx minut xx sekund, použijte funkci TEXT, jak je uvedeno níže:

klikněte TEXT realizovat argumenty a použití této funkce.

Chcete-li vypočítat rozdíl mezi časy v buňce A23 a B23, použijte následující vzorec:

=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").

Stisknout vstoupit klíč k získání výsledku.

Poznámka:

Tento vzorec také vypočítá pouze hodinový rozdíl nepřesahující 24 hodin a end_time musí být větší než start_time, jinak vrátí #HODNOTA! chybová hodnota.

1.27 Vypočítejte rozdíl mezi dvěma daty a časy

Pokud existují dva časy ve formátu mm/dd/rrrr hh:mm:ss, pro výpočet rozdílu mezi nimi můžete podle potřeby použít jeden z níže uvedených vzorců.

Získejte časový rozdíl mezi dvěma daty a vraťte výsledek ve formátu hh:mm:ss

Vezměte dva datum-časy v buňce A2 a B2 jako instanci, použijte prosím vzorec takto:

=B2-A2

Stisknout vstoupit klíč, vracející výsledek ve formátu datum a čas, pak tento výsledek naformátujte jako [h]: mm: ss ve vlastní kategorii pod Číslo záložka Formát buněk dialog.
 

Získejte rozdíl mezi dvěma datumy a návratovými dny, hodinami, minutami, sekundami

Vezměte dva datum-časy v buňce A5 a B5 jako instanci, použijte prosím vzorec takto:

=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "

Stisknout vstoupit klíč k získání výsledku.

Poznámka: V obou vzorcích musí být end_datetime větší než start_datetime, jinak vzorce vrátí chybové hodnoty.

1.28 Vypočítat časový rozdíl v milisekundách

Nejprve musíte vědět, jak formátovat buňku, aby zobrazovala milisekundy:

Vyberte buňky, které chcete zobrazit v milisekundách, a vyberte je doprava Formát buněk k povolení Formát buněk , vyberte Zvyk v Rubrika seznam na kartě Číslo a zadejte toto hh: mm: ss.000 do textového pole.

Použijte vzorec:

Zde pro výpočet rozdílu mezi dvěma časy v buňce A8 a B8 použijte vzorec jako:

=ABS(B8-A8)

Stisknout vstoupit klíč k získání výsledku.

1.29 Vypočítat pracovní dobu mezi dvěma daty kromě víkendů

Někdy může být nutné počítat pracovní dobu mezi dvěma daty, s výjimkou víkendů (sobota a neděle).

Zde je pracovní doba pevně stanovena na 8 hodin každý den a pro výpočet pracovní doby mezi dvěma daty uvedenými v buňce A16 a B16 použijte prosím tento vzorec:

=NETWORKDAYS(A16,B16) * 8

Stisknout vstoupit a poté naformátujte výsledek jako obecný nebo číselný.

Další příklady výpočtu pracovní doby mezi dvěma daty naleznete na adrese Získejte pracovní dobu mezi dvěma daty v aplikaci Excel

Pokud máte Kutools pro Excel instalované v Excelu lze 90 procent výpočtů rozdílu data a času rychle vyřešit, aniž byste si pamatovali nějaké vzorce.

1.31 Výpočet rozdílu mezi dvěma daty a časem pomocí Data & Time Helper

Chcete-li vypočítat rozdíl mezi dvěma datumy v aplikaci Excel, stačí Pomocník pro datum a čas stačí.

1. Vyberte buňku, kam umístíte vypočítaný výsledek, a klepněte Kutools > Pomocník vzorců > Pomocník pro datum a čas.

2. V praskání Pomocník pro datum a čas dialogu, postupujte podle níže uvedených nastavení:

1. Kontrola Rozdíl volba;
2. Vyberte počáteční datum a čas ukončení v Zadání argumentů můžete také přímo ručně zadat datum a čas do vstupního pole nebo kliknout na ikonu kalendáře a vybrat datum;
3. Vyberte typ výsledku výstupu z rozevíracího seznamu;
4. Náhled výsledku v Výsledek sekce.

3. cvaknutí Ok. Vypočte se výsledek a přetáhněte úchyt automatického vyplňování přes buňky, které také potřebujete vypočítat.

Tip:

Pokud chcete získat rozdíl mezi dvěma datem a zobrazit výsledek jako dny, hodiny a minuty s Kutools pro Excel, postupujte prosím následovně:

Vyberte buňku, kam chcete umístit výsledek, a klikněte Kutools > Pomocník vzorců > Datum Čas > Počítejte dny, hodiny a minuty mezi dvěma daty.

Pak v Pomocník vzorců v dialogovém okně zadejte počáteční a koncové datum a klepněte na Ok.

A výsledek rozdílu se zobrazí jako dny, hodiny a minuty.

klikněte Pomocník pro datum a čas abyste se dozvěděli více o použití této funkce.

klikněte Kutools pro Excel znát všechny funkce tohoto doplňku.

klikněte Stažení zdarma získat 30denní bezplatnou zkušební verzi Kutools pro Excel

1.32 Výpočet rozdílu víkend/pracovní den/určitý pracovní den mezi dvěma daty pomocí Pomocníka vzorců

Pokud chcete rychle spočítat víkend, pracovní dny nebo konkrétní všední den mezi dvěma daty, Kutools pro Excel Pomocník vzorců skupina vám může pomoci.

1. Vyberte buňku, do které bude umístěn vypočítaný výsledek, klepněte Kutools > Statistický > Počet nepracovních dnů mezi dvěma daty/Počet pracovních dnů mezi dvěma daty/Spočítejte počet konkrétního dne v týdnu.

2. Ve vyskakovacím okně Pomocník vzorců v dialogovém okně zadejte počáteční a koncové datum, pokud použijete Spočítejte počet konkrétního dne v týdnu, musíte také zadat den v týdnu.

Chcete-li počítat konkrétní den v týdnu, můžete použít poznámku 1-7 pro označení neděle-sobota.

3. cvaknutí Oka pak přetáhněte úchyt automatického vyplňování přes buňky, které potřebují v případě potřeby spočítat počet víkendů/pracovních dnů/určitých dnů v týdnu.

klikněte Kutools pro Excel znát všechny funkce tohoto doplňku.

klikněte Stažení zdarma získat 30denní bezplatnou zkušební verzi Kutools pro Excel

2. Přidejte nebo odečtěte datum a čas

Kromě výpočtu rozdílu mezi dvěma daty a časem je sčítání nebo odečítání také normálním výpočtem data a času v aplikaci Excel. Můžete například chtít získat datum splatnosti na základě data výroby a počtu dnů uchování produktu.

2.11 Přidání nebo odečtení dnů k datu

Chcete-li k datu přidat nebo odečíst určitý počet dní, existují dvě různé metody.

Předpokládejme, že k datu v buňce A21 přidáte 2 dní, vyberte prosím jednu z níže uvedených metod, jak to vyřešit,

Metoda 1 datum+dny

Vyberte buňku a zadejte vzorec:

=A+21

Stisknout vstoupit klíč k získání výsledku.

Pokud chcete odečíst 21 dní, změní se znaménko plus (+) na znaménko mínus (-).

Metoda 2 Vložit speciální

1. Zadejte počet dní, které chcete přidat do buňky, předpokládejme v buňce C2, a stiskněte Ctrl + C kopírovat.

2. Poté vyberte data, ke kterým chcete přidat 21 dní, kliknutím pravým tlačítkem zobrazte kontextovou nabídku a vyberte Vložit speciální....

3. V Vložit jinak dialog, zkontrolujte přidat možnost (Pokud chcete odečíst dny, zaškrtněte Odčítat volba). Klikněte OK.

4. Nyní se původní data změní na 5místná čísla, naformátujte je jako data.

2.12 Přidání nebo odečtení měsíců k datu

Pro přidání nebo odečtení měsíců k datu lze použít funkci EDATE.

klikněte UPRAVIT studovat jeho argumenty a použití.

Předpokládejme, že k datu v buňce A6 přidáte 2 měsíců, použijte vzorec takto:

=EDATE(A2,6)

Stisknout vstoupit klíč k získání výsledku.

Pokud chcete od data odečíst 6 měsíců, změňte 6 na -6.

2.13 Přidání nebo odečtení let k datu

Chcete-li k datu přidat nebo odečíst n let, lze použít vzorec, který kombinuje funkce DATE, YEAR, MONTH a DAY.

Předpokládejme, že k datu v buňce A3 přidáte 2 roky, použijte vzorec takto:

=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))

Stisknout vstoupit klíč k získání výsledku.

Pokud chcete od data odečíst 3 roky, změňte 3 na -3.

2.14 Přidání nebo odečtení týdnů k datu

Chcete-li přidat nebo odečíst týdny k datu, obecný vzorec je

Předpokládejme, že k datu v buňce A4 přidáte 2 týdny, použijte vzorec takto:

=A2+4*7

Stisknout vstoupit klíč k získání výsledku.

Pokud chcete od data odečíst 4 týdny, změňte znaménko plus (+) na znaménko mínus (-).

2.15 Přičíst nebo odečíst pracovní dny včetně svátků nebo bez nich

V této části se seznámíte s tím, jak používat funkci WORKDAY pro přidání nebo odečtení pracovních dnů k danému datu bez svátků nebo včetně svátků.

Návštěva PRACOVNÍ DEN dozvědět se více podrobností o jeho argumentech a použití.

Přidejte pracovní dny včetně svátků

V buňce A2 je datum, které používáte, v buňce B2 je počet dní, které chcete přidat, použijte prosím vzorec takto:

=WORKDAY(A2,B2)

Stisknout vstoupit klíč k získání výsledku.

Přidejte pracovní dny kromě svátků

V buňce A5 je datum, které používáte, v buňce B5 je počet dní, které chcete přidat, v rozsahu D5:D8 jsou uvedeny svátky, použijte prosím vzorec takto:

=WORKDAY(A5,B5,D5:D8)

Stisknout vstoupit klíč k získání výsledku.

Poznámka:

Funkce WORKDAY bere sobotu a neděli jako víkendy, pokud jsou vaše víkendy v sobotu a neděli, můžete použít funkci WOKRDAY.INTL, která podporuje zadávání víkendů.

Návštěva WORKDAY.INTL pro více informací.

Pokud chcete k datu odečíst pracovní dny, stačí změnit počet dní ve vzorci na záporný.

2.16 Přičtěte nebo odečtěte konkrétní rok, měsíc, dny k datu

Pokud chcete k datu přidat konkrétní rok, měsíc, dny, může vám pomoci vzorec, který kombinuje funkce DATUM, ROK, MĚSÍC a DNY.

Chcete-li přidat 1 rok, 2 měsíce a 30 dní k datu v A11, použijte prosím tento vzorec:

=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)

Stisknout vstoupit klíč k získání výsledku.

Pokud chcete odečítat, změňte všechna znaménka plus (+) na znaménka mínus (-).

2.21 Přidání nebo odečtení hodin/minut/sekund k datu a času

Zde jsou uvedeny některé vzorce pro přičítání nebo odečítání hodin, minut nebo sekund k datu a času.

Přidejte nebo odečtěte hodiny k datu a času

Předpokládejme, že přidáte 3 hodiny k datu a času (může to být i čas) v buňce A2, použijte prosím vzorec takto:

=A2+3/24

Stisknout vstoupit klíč k získání výsledku.

Přidejte nebo odečtěte hodiny k datu a času

Předpokládejme, že přidáte 15 minut k datu a času (může to být i čas) v buňce A5, použijte prosím vzorec takto:

=A2+15/1440

Stisknout vstoupit klíč k získání výsledku.

Přidejte nebo odečtěte hodiny k datu a času

Předpokládejme, že přidáte 20 sekund k datu a času (může to být i čas) v buňce A8, použijte prosím vzorec takto:

=A2+20/86400

Stisknout vstoupit klíč k získání výsledku.

2.22 Součet krát za 24 hodin

Předpokládejme, že existuje tabulka Excelu zaznamenávající pracovní dobu všech zaměstnanců za týden, k sečtení celkové pracovní doby pro výpočet plateb můžete použít SUM(rozsah) získat výsledek. Ale obecně se celkový výsledek zobrazí jako čas nepřesahující 24 hodin, jak ukazuje níže uvedený snímek obrazovky, jak můžete získat správný výsledek?

Ve skutečnosti stačí naformátovat výsledek jako [hh]:mm:ss.

Klikněte pravým tlačítkem na buňku s výsledkem, vyberte Formát buněk v kontextové nabídce a ve vyskakovacím okně Formát buněk dialog, zvolte Zvyk z blistru a zadejte [hh]:mm:ss do textového pole v pravé části klikněte OK.
  

Sečtený výsledek se zobrazí správně.

2.23 Přidejte pracovní dobu k datu s výjimkou víkendů a svátků

Zde je uveden dlouhý vzorec pro získání data ukončení na základě přidání konkrétního počtu pracovních hodin k datu zahájení a nezahrnuje víkendy (sobota a neděle) a svátky.

V tabulce aplikace Excel obsahuje A11 počáteční datum a čas a B11 obsahuje pracovní dobu, v buňce E11 a E13 jsou pracovní časy začátku a konce a buňka E15 obsahuje svátek, který bude vyloučen.

Použijte vzorec takto:

=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))

Stisknout vstoupit klíč k získání výsledku.

Pokud máte nainstalovaný Kutools for Excel, pouze jeden nástroj – Datum a čas Helper dokáže vyřešit většinu výpočtů sčítání a odečítání datatime.

1. Klikněte na buňku, ze které chcete vytisknout výsledek, a kliknutím použijte tento nástroj Kutools > Pomocník vzorců > Pomocník pro datum a čas.

2. V Pomocník pro datum a čas dialog, zkontrolujte přidat Možnost nebo Odčítat podle potřeby a poté vyberte buňku nebo přímo zadejte datum a čas, který chcete použít Zadání argumentů poté zadejte roky, měsíce, týdny, dny, hodiny, minuty a sekundy, které chcete přidat nebo odečíst, a poté klikněte Ok. Viz snímek obrazovky:

Náhled vypočteného výsledku můžete zobrazit v Výsledek sekce.

Nyní je výsledek vytištěn, přetáhněte auto handle přes jiné buňky, abyste získali výsledky.

klikněte Pomocník pro datum a čas abyste se dozvěděli více o použití této funkce.

klikněte Kutools pro Excel znát všechny funkce tohoto doplňku.

klikněte Poplatek ke stažení získat 30denní bezplatnou zkušební verzi Kutools pro Excel

2.41 Zaškrtněte nebo zvýrazněte, pokud datum vypršelo

Pokud existuje seznam dat vypršení platnosti produktů, možná budete chtít zkontrolovat a zvýraznit data, která vypršela na základě dneška, jak ukazuje níže uvedený snímek obrazovky.

Ve skutečnosti Podmíněné formátování dokáže tuto práci rychle zvládnout.

1. Vyberte data, která chcete zkontrolovat, a klepněte na Domů > Podmíněné formátování > Nové pravidlo.

2. V Nové pravidlo pro formátování , vyberte Pomocí vzorce určete, které buňky chcete formátovat v Vyberte typ pravidla sekce a zadejte =B2 do vstupního pole (B2 je první datum, které chcete zkontrolovat) a klikněte Formát vyskočit Formát buněk dialogovém okně a poté zvolte jiné formátování, abyste vydrželi data vypršení platnosti, jak potřebujete. Klikněte OK > OK.
  

2.42 Vrátit konec aktuálního měsíce/první den následujícího měsíce/a>

Data expirace některých produktů jsou na konci měsíce výroby nebo prvním dnem následujícího měsíce výroby, pro rychlý seznam dat expirace na základě data výroby postupujte podle této části.

Získejte konec aktuálního měsíce

Zde je datum výroby v buňce B13, použijte prosím vzorec takto:

=EOMONTH(B13,0)

Stisknout vstoupit klíč k získání výsledku.

Získejte 1. den příštího měsíce

Zde je datum výroby v buňce B18, použijte prosím vzorec takto:

=EOMONTH(B18,0)+1

Stisknout vstoupit klíč k získání výsledku.

3. Vypočítejte věk

V této části jsou uvedeny metody řešení, jak vypočítat věk na základě daného data nebo pořadového čísla.

3.11 Vypočítejte věk na základě daného data narození

Získejte věk v desítkovém čísle na základě data narození

klikněte ROČNÍK podrobnosti o jeho argumentech a použití.

Chcete-li například získat věk na základě seznamu dat narození ve sloupci B2:B9, použijte prosím vzorec takto:

=YEARFRAC(B2,TODAY())

Stisknout vstoupit a poté přetáhněte úchyt automatického vyplňování dolů, dokud se nevypočítají všechny věkové skupiny.

Tip:

1) Můžete zadat desetinné místo, jak potřebujete v Formát buněk dialog.

2) Pokud chcete vypočítat věk k určitému datu na základě daného data narození, změňte TODAY() na konkrétní datum uzavřené dvojitými uvozovkami, například =YEARFRAC(B2,"1/1/2021")

3) Pokud chcete získat věk pro příští rok na základě data narození, stačí přidat 1 do vzorce, například =YEARFRAC(B2,TODAY())+1.

Získejte věk v celém čísle na základě data narození

klikněte DATEDIF podrobnosti o jeho argumentech a použití.

Chcete-li pomocí výše uvedeného příkladu získat věk na základě dat narození v seznamu v B2:B9, použijte prosím vzorec takto:

=DATEDIF(B2,TODAY(),"y")

Stisknout vstoupit a poté přetáhněte rukojeť automatického vyplňování dolů, dokud se nevypočítají všechny věky.

Tip:

1) Pokud chcete vypočítat věk k určitému datu na základě daného data narození, změňte TODAY() na konkrétní datum uzavřené dvojitými uvozovkami, například =DATEDIF(B2,"1/1/2021","y") .

2) Pokud chcete získat věk v příštím roce na základě data narození, stačí přidat 1 do vzorce, například =DATEDIF(B2,TODAY(),"y")+1.

3.12 Vypočítejte věk ve formátu roků, měsíců a dnů podle daných narozenin

Pokud chcete vypočítat věk na základě daného data narození a zobrazit výsledek jako xx let, xx měsíců, xx dní, jak ukazuje níže uvedený snímek obrazovky, zde je dlouhý vzorec, který vám může pomoci.

Chcete-li získat věk v letech, měsících a dnech na základě data narození v buňce B12, použijte prosím následující vzorec:

=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"

Stisknout vstoupit , abyste získali věk, pak přetáhněte úchyt automatického vyplňování dolů do jiných buněk.

Tip:

Pokud chcete vypočítat věk k určitému datu na základě daného data narození, změňte TODAY() na konkrétní datum uzavřené dvojitými uvozovkami, například = =DATEDIF(B12,"1/1/2021","Y")& " Roky, "&DATEDIF(B12,"1/1/2021","YM")&" Měsíce, "&DATEDIF(B12,"1/1/2021","MD")&" Dny".

3.13 Vypočítejte věk podle data narození před 1. 1. 1900

V aplikaci Excel nelze datum před 1. 1. 1900 zadat jako datum a čas nebo správně vypočítat. Pokud ale chcete vypočítat věk slavné osobnosti na základě daného data narození (před 1/11900) a data úmrtí, pomůže vám pouze kód VBA.

1. lis Další + F11 klíče k povolení Microsoft Visual Basic pro aplikace okno a klikněte na Vložit kartu a vyberte Modul pro vytvoření nového modulu.

2. Poté zkopírujte a vložte níže uvedený kód do nového modulu.

VBA: Vypočítejte věk před 1. 1. 1900

Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
    Dim xSMonth As Integer
    Dim xSDay As Integer
    Dim xSYear As Integer
    Dim xEMonth As Integer
    Dim xEDay As Integer
    Dim xEYear As Integer
    Dim xAge As Integer
    If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
        AgeFunc = "Invalid Date"
        Exit Function
    End If
    xAge = xEYear - xSYear
    If xSMonth > xEMonth Then
        xAge = xAge - 1
    ElseIf xSMonth = xEMonth Then
        If xSDay > xEDay Then xAge = xAge - 1
    End If
    If xAge < 0 Then
        AgeFunc = "Invalid Date"
    Else
        AgeFunc = xAge
    End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
    Dim I As Long
    Dim K As Long
    Y = 0
    M = 0
    D = 0
    GetDate = True
    On Error Resume Next
    I = InStr(1, DateStr, "/")
    M = CLng(Left(DateStr, I - 1))
    D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
    Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
    If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
        GetDate = False
    End If
End Function

3. Uložte kód a vraťte se na list a vyberte buňku, do které chcete umístit vypočítaný věk, zadejte =AgeFunc(datum narození,datum úmrtí), v tomto případě =AgeFunc(B22,C22), stiskněte klávesu Enter, abyste získali věk. A v případě potřeby použijte úchyt automatického vyplnění k použití tohoto vzorce na další buňky.

Pokud máte Kutools pro Excel nainstalovaný v Excelu, můžete použít Pomocník pro datum a čas nástroj pro výpočet věku.

1. Vyberte buňku, do které chcete umístit vypočítaný věk, a klepněte Kutools > Pomocník vzorců > Pomocník pro datum a čas.

2. V Pomocník pro datum a čas dialog,

• 1) Zkontrolujte věk volba;
• 2) Vyberte buňku data narození nebo přímo zadejte datum narození nebo klikněte na ikonu kalendáře a vyberte datum narození;
• 3) Vyberte Dnes Pokud chcete vypočítat aktuální věk, vyberte Specifikované datum možnost a zadejte datum, chcete-li vypočítat věk v minulosti nebo budoucnosti;
• 4) Určete typ výstupu z rozevíracího seznamu;
• 5) Náhled výsledku výstupu. Klikněte Ok.

klikněte Pomocník pro datum a čas abyste se dozvěděli více o použití této funkce.

klikněte Kutools pro Excel znát všechny funkce tohoto doplňku.

klikněte Stažení zdarma získat 30denní bezplatnou zkušební verzi Kutools pro Excel

3.31 Získejte datum narození z identifikačního čísla

Pokud existuje seznam identifikačních čísel, která používají prvních 6 číslic k zaznamenání data narození, například 920315330 znamená, že datum narození je 03, jak můžete rychle dostat datum narození do jiného sloupce?

Nyní vezměme seznam ID čísel začínajících v buňce C2 jako příklad a použijte vzorec takto:

=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)

Stisknout vstoupit klíč. Poté přetáhněte úchyt automatického vyplňování dolů, abyste získali další výsledky.

Poznámka:

Ve vzorci můžete změnit odkaz na vaši potřebu. Pokud je například ID číslo zobrazené jako 13219920420392, datum narození je 04. 20. 1992, můžete vzorec změnit na =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ "&MID(C2,4,4), abyste získali správný výsledek.

3.32 Vypočítat věk z IČO

Pokud existuje seznam identifikačních čísel, která používají prvních 6 číslic k zaznamenání data narození, například 920315330 znamená, že datum narození je 03. 15. 1992, jak můžete rychle vypočítat věk na základě každého identifikačního čísla v aplikaci Excel?

Nyní vezměme seznam ID čísel začínajících v buňce C2 jako příklad a použijte vzorec takto:

=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")

Stisknout vstoupit klíč. Poté přetáhněte úchyt automatického vyplňování dolů, abyste získali další výsledky.

Poznámka:

V tomto vzorci, pokud je rok menší než aktuální rok, bude rok považován za rok začínající 20, například 200203943 bude považován za rok 2020; pokud je rok větší než aktuální rok, bude rok považován za rok začínající 19, například 920420392 bude považován za rok 1992.

Další výukové programy Excel:

---

Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

• Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
• Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
• Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
• Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
• Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
• Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
• Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
• Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
• Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...

Čtěte více... Plné funkce 30denní bezplatná zkušební verze ... Nákup... 

---

Karta Office - přináší do Office rozhraní s kartami a usnadňuje vám práci

• Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
• Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
• Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!

Čtěte více... Plné funkce 30denní bezplatná zkušební verze ... Nákup...