Dátum a čas v programe Excel sú v programe Excel spracované trochu inak ako v inom softvéri tabuliek. Ak neviete, ako funguje dátum a čas v Exceli, môžu sa vyskytnúť zbytočné chyby.
V tomto článku sa teda dozvieme všetko o dátume a čase Excelu. Naučíme sa, čo sú dátumy v programe Excel, ako pridať čas v programe Excel, ako formátovať dátum a čas v programe Excel, aké sú funkcie dátumu a času v programe Excel, ako vykonávať výpočty dátumu a času (sčítanie, odčítanie, násobenie atď.) s dátumami a časmi).
Čo je dátum a čas v programe Excel?
Mnohí z vás už možno vedia, že dátumy a čas v Exceli nie sú nič iné ako sériové čísla. Dátum je celé číslo a čas je zlomkové číslo. Dátumy v programe Excel majú rôzne regionálne formátovanie. Napríklad v mojom systéme je mm/dd/RRRR (tento formát použijeme v celom článku). Možno používate rovnaký formát dátumu alebo môžete použiť formát dátumu dd/mm/RRRR.
Formátovanie dátumu bunky
Existuje niekoľko možností formátovania dátumu v programe Excel. Vyberte bunku, ktorá môže obsahovať dátum, a stlačte kombináciu klávesov CTRL+1. Otvorí sa dialógové okno Formát buniek. Tu môžete vidieť dve možnosti formátovania ako dátum a čas. V týchto kategóriách je k dispozícii viacero formátovaní dátumu, ktoré vyhovujú vašim požiadavkám.
Termíny:
Dátumy v programe Excel sú poradové čísla kobyly od 1. januára 1900. Deň v programe Excel sa rovná 1. Preto 1. január 1900 je 1, 2. január 1900 je 2 a 1. január 2000 je 36526.
Zábavný fakt: 1900 nebol prestupný rok, ale Excel akceptuje 29. február 1900 ako platný dátum. V tých časoch to bola zúfalá chyba súťažiť s Lotusom 1-2-3.Skratka na zadanie statického dnešného dátumu v programe Excel je CTRL+; (Bodkočiarka).
Ak chcete pridať alebo odpočítať deň od dátumu, stačí k tomuto dátumu odpočítať alebo pridať počet dní.
Čas:
Program Excel štandardne dodržiava formát hh: mm pre čas (formát 0 až 23). Hodiny a minúty sú oddelené dvojbodkou bez medzier. Môžete ho zmeniť na formát hh: mm AM/PM. AM/PM musí mať 1 medzeru od hodnoty času. Ak chcete zahrnúť sekundy, môžete pridať: ss do hh: mm (hh: mm: ss). Akýkoľvek iný časový formát je neplatný.
Čas je vždy spojený s dátumom. Dátum je pred hodnotou času oddelenou medzerou od času. Ak dátum nespomínate vopred, štandardne sa použije prvý dátum v programe Excel (čo je 1/1/1900). Čas v Exceli je zlomkové číslo. Zobrazuje sa na pravej strane desatinnej čiarky.
Hodiny:
Pretože 1 deň sa rovná 1 v programe Excel a 1 deň pozostáva z 24 hodín, 1 hodina sa rovná 1/24 v programe Excel. Čo to znamená? Znamená to, že ak chcete sčítať alebo odčítať 1 hodinu časovo, musíte pridať alebo odčítať 1/24. Pozrite sa na obrázok nižšie.
Môžete povedať, že 1 hodina sa rovná 0,041666667 (1/24).
Vypočítajte hodiny medzi časom v programe Excel
Zápisnica:
Z vysvetlenia hodiny v programe Excel ste určite uhádli, že 1 minúta v programe Excel sa rovná 1/(24 x 60) (alebo 1/1440 alebo0.000694444).
Ak chcete pridať minútu k excelentnému času, pridajte 1/(24 x 60).Pozrite sa na obrázok nižšie. Niekedy máte potrebu vypočítať minúty medzi dátumami a časom V programe Excel si to môžete prečítať tu.
Sekundy:
Áno, sekunda v programe Excel sa rovná1/(24 x 60 x 60).Ak chcete z času sčítať alebo odčítať sekundy, stačí urobiť to isté, čo sme urobili za minúty a hodiny.
Dátum a čas v jednej bunke
Dátumy a časy sú navzájom prepojené. Dátum je vždy spojený s platným dátumom a čas je vždy spojený s platným dátumom programu Excel. Aj keď jedného z nich nemôžete vidieť.
Ak do bunky zadáte iba čas, dátum tejto bunky bude 1. januára 1900, aj keď ho nemôžete vidieť. Ak naformátujete túto bunku vo formáte dátumu a času, uvidíte príslušný dátum. Podobne, ak s dátumom neuvádzate čas, v predvolenom nastavení je pripojená 00:00. Pozrite sa na obrázok nižšie.
Na obrázku vyššie máme čas iba v B3 a dátum iba v B4. Keď tieto bunky naformátujeme ako mm/dd/rr hh: mm, v oboch bunkách dostaneme čas aj dátum.
Pri výpočtoch dátumu a času v programe Excel to preto kontrolujte.
Žiadny negatívny čas
Ako som vám povedal, dátum a čas v programe Excel začínajú od 1. januára 1900 o 00:00. Kedykoľvek predtým to nie je platný dátum v programe Excel. Ak odčítate hodnotu z dátumu, ktorý vedie pred 1. januárom 1900, 12:00, dokonca aj za jednu sekundu, program Excel zobrazí chybu #######. Hovoril som o tom tu a v časti Previesť dátum a čas z GMT na CST. Stáva sa to, keď sa pokúsime odpočítať niečo, čo vedie k 1. januáru 1900-12:00. Skúste to sami. Napíšte 12:00 a odčítajte z nej 13 hodín. pozrite sa, čo dostanete.
Výpočty s dátumami a časom v programe Excel
Pridanie dní k dátumu:
Pridanie dní do dátumu v programe Excel je jednoduché. Ak chcete pridať deň k dátumu, stačí k nemu pridať 1 deň. Pozrite sa na obrázok nižšie.
Nemali by ste pridávať dva dátumy, aby ste získali budúci dátum, pretože to zhrnie sériové čísla týchto dní a môžete dostať dátum ďaleko v budúcnosti.
Odčítanie dní od dátumu:
Ak chcete získať spätný dátum z dátumu pred niekoľkými dňami, stačí odpočítať tento počet dní od dátumu a získate spätný dátum. Napríklad, ak chcem vedieť, aký dátum bol pred 56 dňami od DNES, potom by som do bunky napísal tento vzorec.
| = DNES ()-56 |
Tým sa nám vráti dátum 56 pred aktuálnym dátumom.
Poznámka: Pamätajte si, že nemôžete mať dátum pred 1. januárom/1900 v Exceli. Ak sa zobrazí chyba ######, môže to byť dôvod.
Dni medzi dvoma dátumami:
Na výpočet dní medzi dvoma dátumami stačí odpočítať dátum začiatku od dátumu ukončenia. Na túto tému som už urobil článok. Choďte a overte si to tu. Na výpočet dní medzi počiatočným a koncovým dátumom môžete použiť aj funkciu Excel DAYS.
Čas pridania:
Vyskytlo sa veľa otázok o tom, ako pridať čas vyniknutiu, pretože veľa ľudí pri tom dostane mätúce výsledky. Existujú dva typy pridávania v čase. Jednou je pridanie času inému času. V tomto prípade sú obidva časy formátované ako časový formát hh: mm. V tomto prípade môžete tieto časy jednoducho pridať.
Druhý prípad je, keď nemáte viac času v časovom formáte. Stačí pridať hodiny, minúty a sekundy. V takom prípade musíte tieto čísla previesť na ich časové ekvivalenty. Všimnite si tieto body, aby ste k dátumu/času pridali hodiny, minúty a sekundy.
- Ak chcete pridať X hodín k času X, použite vzorec = X+(N/24) (As 1 = 24 hodín)
- Na pridanie N minút do X času použite vzorec = X+(N/(24*60))
- Na pridanie N sekundy k času X použite vzorec = X+(N/(24*60*60))
Odčítanie časov
Je to rovnaké ako sčítanie času, len sa uistite, že pri odčítaní neskončíte so zápornou hodnotou času, pretože v Exceli nič také ako záporné číslo neexistuje.
Poznámka: Keď v programe Excel pridáte alebo odčítate čas, ktorý presahuje 24 hodín rozdielu, program Excel sa presunie na nasledujúci alebo predchádzajúci dátum. Ak napríklad odpočítate 2 hodiny od 29. januára 2019 1:00, potom sa vráti na 28. januára 2019 o 23:00. Ak odčítate 2 hodiny od 1:00 (nemá uvedený dátum), Excel vráti chybu ######. Dôvod som uviedol na začiatku článku.Pridanie mesiacov k dátumu:K dnešnému dňu nemôžete pridať iba násobky 30, pretože rôzne mesiace majú rôzny počet dní. Pri pridávaní mesiacov do Dátumu musíte byť opatrní. Na pridanie mesiacov k dátumu v programe Excel používame funkciu EDATE v programe Excel. Tu mám samostatný článok o pridávaní mesiacov k dátumu v rôznych scenároch.
Pridanie rokov k dátumu:
Rovnako ako pridanie mesiacov k dátumu nie je jednoduché pridať k dátumu roky. Na pridanie rokov k dátumu musíme použiť funkciu YEAR, MONTH, DAY. Tu si môžete prečítať o pridaní súčasných rokov.
Ak chcete vypočítať roky medzi dátumami, môžete to použiť.
Funkcie spracovania dátumu a času v Exceli:
Pretože dátum a čas sú v programe Excel špeciálne, Excel poskytuje na ich spracovanie špeciálne funkcie. Tu uvádzam niekoľko z nich.
- DNES (): Táto funkcia dynamicky vracia dnešný dátum.
- DAY (): Vráti sa deň v mesiaci (vráti číslo 1 až 31).
- DAYS (): Používa sa na počítanie dní medzi dvoma dátumami.
- MONTH (): Používa sa na získanie mesiaca dátumu (vráti číslo 1 až 12).
- YEAR (): Vráti rok dátumu.
- WEEKNUM (): Vráti týždenné číslo dátumu v roku.
- WEEKDAY (): Vráti číslo dňa za týždeň (1 až 7) zadaného dátumu.
- WORKDAY (): Používa sa na výpočet pracovných dní.
- TIMEVALUE (): Používa sa na extrahovanie hodnoty času (sériové číslo) z dátumu a času vo formáte.
- DATEVALUE (): Používa sa na extrakciu hodnoty dátumu (sériového čísla) z dátumu a času formátovaného v texte.
Toto sú niektoré z najužitočnejších dátových a časových funkcií v Exceli. Existuje mnoho ďalších funkcií dátumu a času. Môžete si ich pozrieť tu.
Výpočty dátumu a času
Ak tu vysvetlím všetky, tento článok bude príliš dlhý. Tieto techniky výpočtu času v Exceli som rozdelil na samostatné články. Tu ich spomínam. Kliknutím na ne môžete čítať.
- Vypočítajte dni, mesiace a roky
- Vypočítajte vek od dátumu narodenia
- Násobenie časových hodnôt a čísel.
- Získajte názov mesiaca od dátumu v programe Excel
- Získajte názov dňa od dátumu v programe Excel
- Ako získať štvrť roka od dátumu
- Ako pridať pracovné dni v programe Excel
- Vložte pečiatku dátumu a času s VBA
Takže áno, chlapci, toto je všetko o dátume a čase v Exceli, o ktorom potrebujete vedieť. Dúfam, že vám bol tento článok užitočný. Ak máte akékoľvek otázky alebo návrhy, napíšte ich do nižšie uvedenej sekcie komentárov.