Existuje mnoho spôsobov, ako previesť dátumy naformátované ako text na skutočné dátumy v Exceli a cieľom tohto návodu je preskúmať ich všetky.
Keď importujete údaje do Excelu, môžu mať mnoho rôznych foriem, ktoré Excel nerozpozná. Niekedy, keď sa dátumy importujú z externého zdroja, prídu vo formáte textu.
Je tiež dôležité vedieť, že Excel používa formáty dátumu na základe nastavení oblasti systému. Keď teda do Excelu importujete údaje, ktoré pochádzajú z inej krajiny, Excel ich nemusí rozpoznať a uložiť ako textové položky.
Ak máte tento problém, existuje mnoho spôsobov, ktoré môžete použiť na konverziu textu na dátum v Exceli a všetky ich pokryjeme v tomto návode.
Metódy prevodu textu na dátum
Ak váš pracovný hárok obsahuje dátumy vo formáte textu namiesto skutočných dátumov, nie je možné ich použiť v žiadnych výpočtoch. Takže ich musíte previesť späť na skutočné dátumy.
Ak vidíte svoje dátumy zarovnané vľavo, znamená to, že sú naformátované ako text, pretože texty sú predvolene zarovnané doľava. A číslo a dátumy sú vždy zarovnané vpravo.
Existuje niekoľko rôznych spôsobov, ako môžete previesť text na dátum v Exceli, sú to:
- Možnosť kontroly chýb
- Funkcia Excel Text to Columns
- Nájsť a nahradiť
- Prilepiť špeciálny nástroj
- Vzorec a funkcie Excelu
Previesť text na dátum pomocou možnosti kontroly chýb
Excel má vstavanú funkciu kontroly chýb, ktorá odhalí niektoré zjavné chyby vo vašich údajoch. Ak nájde nejakú chybu, zobrazí vám malý zelený trojuholník (indikátor chyby) v ľavom hornom rohu bunky, ktorá obsahuje chybu. Ak vyberiete túto bunku, zobrazí sa výstražný znak so žltým výkričníkom. Keď prejdete kurzorom na tento znak, Excel vás bude informovať o možnom probléme s touto bunkou.
Napríklad, keď do dátumu zadáte rok v dvojcifernom formáte, Excel tento dátum prevezme ako text a uloží ho ako text. A ak vyberiete túto bunku, zobrazí sa výkričník s upozornením: „Táto bunka obsahuje reťazec dátumu reprezentovaný iba 2 číslicami roku“.
Ak sa vo vašich bunkách zobrazuje indikátor chyby, kliknite na výkričník a zobrazí sa niekoľko možností na konverziu dátumov vo formáte textu na skutočné dátumy. Excel vám ukáže možnosti prevodu na 19XX alebo 20XX (19XX pre 1915, 20XX pre 2015). Vyberte príslušnú možnosť.
Potom sa text skonvertuje do správneho formátu dátumu.
Ako povoliť možnosť kontroly chýb v programe Excel
Zvyčajne je možnosť Kontrola chýb v Exceli predvolene zapnutá. Ak vám funkcia kontroly chýb nefunguje, musíte povoliť kontrolu chýb v Exceli.
Ak to chcete urobiť, kliknite na kartu „Súbor“ a na ľavom paneli vyberte „Možnosti“.
V okne Možnosti programu Excel kliknite na ľavom paneli na „Vzorce“ a na pravom paneli povoľte v časti Kontrola chýb možnosť „Povoliť kontrolu chýb na pozadí“. A začiarknite políčko „Bunky obsahujúce roky vyjadrené ako 2 číslice“ v časti Pravidlá kontroly chýb.
Prevod textu na dátum pomocou funkcie Excel Text na stĺpec
Text do stĺpca je skvelá funkcia v Exceli, ktorá vám umožňuje rozdeliť údaje do viacerých stĺpcov, a tiež je to výkonný nástroj na prevod textových hodnôt na dátumové hodnoty. Táto metóda rozpoznáva niekoľko rôznych formátov údajov a konvertuje ich do správneho formátu dátumu.
Prevod jednoduchých textových reťazcov na dátumy
Povedzme, že vaše dátumy sú formátované v textových reťazcoch takto:
Môžete použiť sprievodcu Text to Columns na rýchle preformátovanie všetkých späť na dátumy.
Najprv vyberte rozsah textových položiek, ktoré chcete previesť na dátumy. Potom prejdite na kartu „Údaje“ na páse s nástrojmi a kliknite na možnosť „Text do stĺpcov“ v skupine Nástroje údajov.
Zobrazí sa sprievodca Text to Columns. V kroku 1 sprievodcu Text do stĺpca vyberte možnosť „Oddelené“ v časti Pôvodný typ údajov a kliknite na tlačidlo „Ďalej“.
V kroku 2 zrušte začiarknutie všetkých políčok „Oddeľovače“ a kliknite na tlačidlo Ďalej.
V poslednom kroku sprievodcu vyberte „Dátum“ v časti Formát údajov stĺpca a vyberte formát dátumu z rozbaľovacieho zoznamu vedľa „Dátumy“ a kliknite na tlačidlo „Dokončiť“. V našom prípade konvertujeme textové dátumy reprezentované ako „01 02 1995“ (deň mesiac rok), takže z rozbaľovacieho zoznamu „Dátum:“ vyberieme „DMY“.
Teraz Excel prevedie dátumy textu na skutočné dátumy a zobrazí ich v bunkách zarovnané doprava.
Poznámka: Skôr ako začnete používať funkciu Text to Column, uistite sa, že všetky vaše textové reťazce majú rovnaký formát, inak sa texty neprevedú. Ak sú napríklad niektoré dátumy textu vo formáte mesiac/deň/rok (MDY), zatiaľ čo iné sú vo formáte deň/mesiac/rok (DMY), a keď v kroku 3 vyberiete „DMY“, dostanete nesprávne výsledky. Ako je znázornené na obrázku nižšie.
Konverzia zložitého textového reťazca na dátum
Funkcia Text to Columns sa hodí, keď chcete previesť zložité textové reťazce na dátumy. Umožňuje vám pomocou oddeľovačov určiť, kde by sa mali vaše údaje rozdeliť a zobraziť v 2 alebo viacerých stĺpcoch. A skombinujte rozdelené časti dátumov do celého dátumu pomocou funkcie DATE.
Ak sa napríklad dátumy zobrazujú v textových reťazcoch s viacerými časťami, ako je tento:
Streda 1. februára 2020
1. februára 2020, 16:10
Pomocou sprievodcu Text do stĺpca môžete oddeliť informácie o dni, dátume a čase, ktoré sú oddelené čiarkou, a zobraziť ich vo viacerých stĺpcoch.
Najprv vyberte všetky textové reťazce, ktoré chcete previesť na dátumy. Kliknite na tlačidlo „Text do stĺpcov“ na karte „Údaje“. V kroku 1 sprievodcu Text do stĺpca vyberte možnosť „Oddelené“ v časti Pôvodný typ údajov a kliknite na tlačidlo „Ďalej“.
V kroku 2 sprievodcu vyberte oddeľovače, ktoré obsahujú vaše textové reťazce, a kliknite na tlačidlo „Ďalej“. Naše vzorové textové reťazce oddelené čiarkou a medzerou – „pondelok, 1. február 2015, 13:00“. Ako oddeľovače by sme mali zvoliť „Čiarka“ a „medzery“, aby sme rozdelili textové reťazce do viacerých stĺpcov.
V poslednom kroku vyberte formát „Všeobecné“ pre všetky stĺpce v časti Ukážka údajov. V poli „Cieľ“ zadajte, kam sa majú stĺpce vložiť, ak tak neurobíte, prepíšu sa pôvodné údaje. Ak chcete ignorovať niektorú časť pôvodných údajov, kliknite na ňu v časti Náhľad údajov a vyberte možnosť „Neimportovať stĺpec (preskočiť)“. Potom kliknite na „Dokončiť“.
Teraz sú časti dátumov (dni v týždni, mesiac, rok, čas) rozdelené do stĺpcov B, C, D, E, F a G.
Potom skombinujte časti dátumu pomocou vzorca DÁTUM, aby ste získali celý dátum.
Syntax funkcie Excel DATE:
=DATE(rok,mesiac,deň)V našom príklade sú časti mesiaca, dňa a roku v stĺpcoch C, D a E.
Funkcia DATE rozpoznáva iba čísla, nie text. Keďže naše mesačné hodnoty v stĺpci C sú všetky textové reťazce, musíme ich previesť na čísla. Na to je potrebné pomocou funkcie MONTH zmeniť názov mesiaca na číslo mesiaca.
Ak chcete previesť názov mesiaca na číslo mesiaca, použite túto funkciu MONTH vo funkcii DATE:
=MONTH(1&C1)Funkcia MONTH pridá 1 do bunky C2, ktorá obsahuje názov mesiaca, aby sa názov mesiaca previedol na zodpovedajúce číslo mesiaca.
Toto je funkcia DATE, ktorú musíme použiť na kombinovanie častí dátumu z rôznych stĺpcov:
=DÁTUM(E1,MESIAC(1&C1);D1)
Teraz použite rukoväť výplne v dolnom rohu bunky vzorca a aplikujte vzorec na stĺpec.
Prevod textu na dátum pomocou metódy hľadania a nahradenia
Táto metóda používa oddeľovače na zmenu formátu textu na dátumy. Ak sú deň, mesiac a rok v dátumoch oddelené nejakým oddeľovačom iným ako pomlčkou (-) alebo lomkou (/), Excel ich nerozpozná ako dátumy a bude ich ukladať ako text.
Ak chcete tento problém vyriešiť, použite funkciu Nájsť a nahradiť. Zmenou neštandardných oddeľovačov období (.) s lomkami (/) alebo pomlčkami (-) Excel automaticky zmení hodnoty na dátumy.
Najprv vyberte všetky dátumy textu, ktoré chcete previesť na dátumy. Na karte „Domov“ kliknite na tlačidlo „Nájsť a vybrať“ v pravom rohu pásu s nástrojmi a vyberte možnosť „Nahradiť“. Prípadne stlačte Ctrl+H otvorte dialógové okno Nájsť a nahradiť.
V dialógovom okne Nájsť a nahradiť zadajte oddeľovač, ktorý váš text obsahuje (v našom prípade bodku (.) do poľa „Nájsť čo“ a lomku (/) alebo pomlčku (-) do poľa „Nahradiť čím“). Kliknutím na tlačidlo 'Nahradiť všetko' nahraďte oddeľovače a kliknutím na tlačidlo 'Zatvoriť' zatvorte okno.
Teraz Excel rozpozná, že vaše textové reťazce sú teraz dátumy a automaticky ich naformátuje ako dátumy. Vaše dátumy budú zarovnané správne, ako je uvedené nižšie.
Previesť text na dátum pomocou špeciálneho nástroja na prilepenie
Ďalším rýchlym a jednoduchým spôsobom, ako previesť textové reťazce na dátumy, je pridanie 0 do textového reťazca pomocou špeciálnej možnosti prilepenia. Pridaním nuly k hodnote sa text prevedie na sériové číslo dátumu, ktoré môžete naformátovať ako dátum.
Najprv vyberte prázdnu bunku a skopírujte ju (označte ju a stlačte Ctrl + C kopírovať).
Potom vyberte bunky obsahujúce dátumy textu, ktoré chcete previesť, kliknite pravým tlačidlom myši a vyberte možnosť „Prilepiť špeciálne“.
V dialógovom okne Prilepiť špeciálne vyberte „Všetko“ v časti Prilepiť, v časti Operácia vyberte „Pridať“ a kliknite na „OK“.
Môžete tiež vykonávať ďalšie aritmetické operácie odčítanie/násobenie/delenie hodnoty v cieľovej bunke s vloženou hodnotou (napríklad násobenie buniek 1 alebo delenie 1 alebo odčítanie nuly).
Keď v Prevádzke vyberiete možnosť „Pridať“, pridá sa ku všetkým dátumom textu „nula“, keďže pridanie „0“ nezmení hodnoty, pre každý dátum získate sériové číslo. Teraz všetko, čo musíte urobiť, je zmeniť formát buniek.
Vyberte sériové čísla a na karte „Domov“ kliknite na rozbaľovací zoznam „Formát čísel“ v skupine Číslo. V rozbaľovacej ponuke vyberte možnosť „Krátky dátum“.
Ako teraz vidíte, čísla sú naformátované ako dátumy a zarovnané doprava.
Prevod textu na dátum pomocou vzorcov
Na prevod textu na dátum sa primárne používajú dve funkcie: DATEVALUE a VALUE.
Použitie funkcie DATEVALUE v Exceli
Funkcia Excel DATEVALUE je jedným z najjednoduchších spôsobov, ako previesť dátum reprezentovaný ako text na sériové číslo dátumu.
Syntax funkcie DATEVALUE:
=DATEVALUE(date_text)argument: dátum_text určuje textový reťazec, ktorý chcete skryť alebo odkazovať na bunku obsahujúcu textové dátumy.
Vzorec:
=DATEVALUE(A1)Nasledujúci obrázok ilustruje, ako funkcia DATEVALUE spracováva niekoľko rôznych formátov dátumu, ktoré sú uložené ako text.
Máte sériové čísla dátumu, teraz musíte na tieto čísla použiť formát dátumu. Ak to chcete urobiť, vyberte bunky so sériovými číslami, potom prejdite na kartu „Domov“ a v rozbaľovacom zozname „Formát čísel“ vyberte „Krátky dátum“.
Teraz máte naformátované dátumy v stĺpci C.
Aj keď v dátume textu (A8) nie je žiadna časť s rokom, DATEVALUE použije aktuálny rok z hodín vášho počítača.
Funkcia DATEVALUE skonvertuje iba textové hodnoty, ktoré vyzerajú ako dátum. Nedokáže previesť text, ktorý sa podobá číslu na dátum, ani nemôže zmeniť číselnú hodnotu na dátum, na to potrebujete funkciu VALUE v Exceli.
Použitie funkcie Excel VALUE
Funkcia Excel VALUE dokáže previesť ľubovoľný textový reťazec, ktorý sa podobá dátumu alebo číslu, na číselnú hodnotu, takže je skutočne užitočná, pokiaľ ide o prevod akéhokoľvek čísla, nielen dátumov.
Funkcia VALUE:
=VALUE(text)text– textový reťazec, ktorý chceme previesť, alebo odkaz na bunku obsahujúcu textový reťazec.
Príklad vzorca na prevod dátumu textu:
=HODNOTA(A1)Nižšie uvedený vzorec VALUE môže zmeniť ľubovoľné textové reťazce, ktoré vyzerajú ako dátum, na číslo, ako je uvedené nižšie.
Funkcia VALUE však nepodporuje všetky typy hodnôt dátumu. Napríklad, ak dátumy používajú desatinné miesta (A11), vráti #HODNOTA! chyba.
Keď budete mať sériové číslo pre svoj dátum, budete musieť naformátovať bunku so sériovým číslom dátumu, aby vyzerala ako dátum, ako sme to urobili pre funkciu DATEVALUE. Ak to chcete urobiť, vyberte sériové čísla a vyberte možnosť „Dátum“ z rozbaľovacej ponuky „Formát čísla“ na karte „Domov“.
To je všetko, toto je 5 rôznych spôsobov, ako môžete previesť dátumy vo formáte textu na dátumy v Exceli.