Excel ukladá hodnoty dátumu a času ako sériové čísla do servera typu back -end.
To znamená, že aj keď sa v bunke môže zobraziť dátum ako „10. Januára 2021-2022“ alebo „01/10/2020“, v back-ende to Excel ukladá ako číslo. Je to užitočné, pretože tiež umožňuje používateľom jednoducho pridávať/odčítať dátum a čas v programe Excel.
V programe Microsoft Excel pre Windows je „1. január 1900“ uložený ako 1, „02. januára 1900“ je uložený ako 2 a podobne.
Oba stĺpce majú nižšie uvedené čísla, ale v stĺpci A sú uvedené čísla a v stĺpci B dátum, ktorý je týmto číslom reprezentovaný.
To je tiež dôvod, prečo niekedy môžete očakávať rande v bunke, ale nakoniec uvidíte číslo. Vidím, že sa to deje stále, keď sťahujem údaje z databáz alebo webových nástrojov.
Stáva sa to vtedy, keď je formát bunky nastavený tak, aby namiesto dátumu zobrazoval číslo ako číslo.
Ako teda môžeme? previesť tieto sériové čísla na dátumy?
O tom je tento návod.
V tomto návode by som vám ukázal dva skutočne jednoduché spôsoby prevodu sériových čísel na dátumy v Exceli
Poznámka: Excel pre Windows používa dátumový systém 1900 (čo znamená, že 1 by predstavoval 1. január 1900, zatiaľ čo Excel pre Mac používa dátumový systém 1904 (čo znamená, že 1 by predstavovalo 1. januára 1904 v Exceli v Macu)Začnime teda!
Konvertujte sériové čísla na dátumy pomocou formátovania čísel
Najjednoduchší spôsob, ako previesť sériové číslo dátumu na dátum, je zmena formátovania buniek, ktoré majú tieto čísla.
Niektoré z bežne používaných formátov dátumu nájdete na karte Domov na páse s nástrojmi
Ukážem vám, ako to urobiť.
Použitie vstavaných možností formátu dátumu na páse s nástrojmi
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete previesť všetky tieto čísla v stĺpci A na zodpovedajúci dátum, ktorý predstavuje.
Nasledujú kroky, ako to urobiť:
- Vyberte bunky s číslom, ktoré chcete previesť na dátum
- Kliknite na kartu „Domov“
- V skupine „Číslo“ kliknite na rozbaľovaciu ponuku Formátovanie čísla
- V rozbaľovacom zozname vyberte možnosť „Dlhý dátum“ alebo Krátky dátum (podľa toho, v akom formáte chcete, aby boli tieto čísla)
To je všetko!
Vyššie uvedené kroky by prevádzali čísla do vybratého formátu dátumu.
Vyššie uvedené kroky nezmenili hodnotu v bunke, iba spôsob, akým sa zobrazuje.
Všimnite si toho, že Excel vyberá krátke formátovanie dátumu na základe regionálneho nastavenia vášho systému. Ak ste napríklad v USA, formát dátumu by bol MM/DD/RRRR a ak ste vo Veľkej Británii, formát dátumu by bol DD/MM/RRRR.
Aj keď je to rýchly spôsob prevodu sériových čísel na dátumy, má niekoľko obmedzení:
- Existujú iba dva formáty dátumu - krátky dátum a dlhý dátum (a to aj v konkrétnom formáte). Ak teda chcete získať iba mesiac a rok, a nie deň, pomocou týchto možností to nedokážete.
- Pomocou možností formátovania v rozbaľovacom zozname nemôžete zobrazovať dátum ani čas. Môžete buď zvoliť zobrazenie dátumu alebo času, ale nie oboje.
Ak teda potrebujete väčšiu flexibilitu v spôsobe zobrazovania dátumov v programe Excel, musíte použiť možnosť Vlastné formátovanie čísel (popísané ďalej v tomto návode).
Niekedy keď konvertujete číslo na dátum v programe Excel, namiesto dátumu sa vám môžu zobraziť znaky hash (niečo ako #####). k tomu dôjde vtedy, keď šírka stĺpca nepostačuje na prispôsobenie celého dátumu. V takom prípade stačí zväčšiť šírku stĺpca.Vytvorenie vlastného formátu dátumu pomocou dialógového okna Formátovanie čísla
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete previesť sériové čísla v stĺpci A na dátumy v nasledujúcom formáte - 01.01.2020.
Všimnite si toho, že táto metóda nie je predvolene k dispozícii v metóde pásky, ktorou sme sa zaoberali vyššie.
Nasledujú kroky, ako to urobiť:
- Vyberte bunky s číslom, ktoré chcete previesť na dátum
- Kliknite na kartu „Domov“
- V skupine Čísla kliknite na ikonu spúšťača dialógového okna (je to malá naklonená šípka v pravom dolnom rohu skupiny)
- V dialógovom okne „Formát buniek“, ktoré sa otvorí, skontrolujte, či je vybratá karta „Číslo“
- V možnostiach „Kategória“ vľavo vyberte „Dátum“
- V poli „Typ“ vyberte požadované formátovanie.
- Kliknite na tlačidlo OK
Vyššie uvedené kroky by prevádzali čísla do vybratého formátu dátumu.
Ako vidíte, v tomto prípade existuje viac možností formátovania dátumu (v porovnaní s krátkym a dlhým dátumom, ktoré sme dostali s pásikom).
A keby ste nenašli požadovaný formát, môžete si vytvoriť aj vlastný formát dátumu.
Povedzme napríklad, že chcem zobraziť dátum v nasledujúcom formáte - 01/01/2020 (čo v dialógovom okne buniek formátu už nie je možné).
Takto to môžem urobiť:
- Vyberte bunky s číslami, ktoré chcete previesť na dátum
- Kliknite na kartu Domov
- V skupine Čísla kliknite na ikonu spúšťača dialógového okna (je to malá šípka naklonenia v pravom dolnom rohu skupiny)
- V dialógovom okne „Formát buniek“, ktoré sa otvorí, skontrolujte, či je vybratá karta „Číslo“
- V možnostiach „Kategória“ vľavo vyberte „Vlastné“
- Do poľa vpravo zadajte
mm/dd/rrrr
- Kliknite na tlačidlo OK
Vyššie uvedené kroky zmenia všetky čísla na zadaný formát čísel.
Vďaka vlastnému formátu získate úplnú kontrolu a umožní vám zobraziť dátum v ľubovoľnom požadovanom formáte. Môžete tiež vytvoriť formát, v ktorom sa v rovnakej bunke zobrazuje dátum a čas.
Napríklad v tom istom príklade, ak chcete zobraziť dátum aj čas, môžete použiť nasledujúci formát:
mm/dd/rrrr hh: mm AM/PM
Nasledujúca tabuľka ukazuje kódy formátu dátumu, ktoré môžete použiť:
| Kód formátovania dátumu | Ako formátuje dátum |
| m | Zobrazuje mesiac ako číslo od 1 do 12 |
| mm | Ukážte mesiace ako dvojciferné čísla od 1. do 12 |
| mmm | Ukazuje mesiac ako trojpísmenový ako v januári až decembri |
| mmmm | Zobrazuje úplný názov mesiacov ako január až december |
| mmmmm | Zobrazuje prvú abecedu názvu mesiaca ako v J-D |
| d | Zobrazuje dni od 1 do 31 |
| dd | Zobrazuje dni od 1. do 31. januára |
| ddd | Ukazuje dni ako ne-so |
| dddd | Ukazuje dni ako nedeľa-sobota |
| r | Ukazuje roky ako 00-99 |
| rrrr | Ukazuje roky 1900-9 999 |
Premeňte sériové čísla na dátumy pomocou vzorca TEXT
Vyššie uvedené metódy fungujú tak, že sa zmení formátovanie buniek s číslami.
V niektorých prípadoch však tieto metódy nebudete môcť použiť.
Napríklad nižšie mám súbor údajov, kde majú čísla počiatočný apostrof - ktorý tieto čísla prevádza na text.
Ak sa pokúsite pomocou vstavaných možností zmeniť formátovanie buniek s týmto súborom údajov, nič by to neurobilo. Dôvodom je, že Excel ich nepovažuje za čísla, a keďže dátumy pre čísla, Excel odmieta vaše želanie ich formátovať.
V takom prípade môžete buď previesť tento text na čísla a potom použiť vyššie uvedené metódy formátovania, alebo použiť inú techniku na prevod sériových čísel na dátumy - pomocou funkcie TEXT.
Funkcia TEXT má dva argumenty - číslo, ktoré je potrebné naformátovať, a formátovací kód.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete previesť všetky čísla a stĺpec A na dátumy.
Pod vzorcom, ktorý by to mohol urobiť:
= TEXT (A2, "MM/DD/RRRR")
Všimnite si toho, že vo vzorci som určil formátovanie dátumu vo formáte MM/DD/RRRR. Ak potrebujete použiť akékoľvek iné formátovanie, môžete preň zadať kód ako druhý argument vzorca TEXT.
Tento vzorec môžete použiť aj na zobrazenie dátumu a času.
Ak napríklad chcete, aby bol konečný výsledok vo formáte - 01/01/2020 0:00, použite nasledujúci vzorec:
= TEXT (A2, "mm/dd/rrrr hh: mm AM/PM")
Do vyššie uvedeného vzorca som pridal aj časový formát, takže ak sú v číslach desatinné časti, zobrazí sa ako čas v hodinách a minútach.
Ak chcete iba dátum (a nie základný vzorec), preveďte vzorce na statické hodnoty pomocou príkazu prilepiť špeciálne.
Jednou z veľkých výhod použitia funkcie TEXT na prevod sériových čísel na dátumy je to, že môžete výsledok vzorca TEXT kombinovať s inými vzorcami. Výsledný dátum môžete napríklad skombinovať s textom a ukázať niečo ako - Termín splatnosti je 01. januára 2020 alebo Termín je 01. januára 2020
Poznámka: Pamätajte si, že dátumy a čas sú uložené ako čísla, kde každé celé číslo predstavuje jeden celý deň a desatinná časť predstavuje veľkú časť dňa. 1 by teda znamenalo 1 celý deň a 0,5 by znamenalo pol dňa alebo 12 hodín.
Toto sú teda dva jednoduché spôsoby, ktoré môžete použiť na prevod sériových čísel na dátumy v programe Microsoft Excel.
Dúfam, že ste našli tento návod užitočný!
