Všeobecný vzorec
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Zhrnutie
Ak chcete previesť text v nerozpoznanom formáte dátumu na správny dátum v programe Excel, môžete analyzovať text a zostaviť správne dátum pomocou vzorca založeného na niekoľkých funkciách: DATE, LEFT, MID a RIGHT. V zobrazenom príklade je vzorec v C6:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Tento vzorec extrahuje hodnoty roku, mesiaca a dňa osobitne a na ich zostavenie do dátumu 24. októbra 2000 používa funkciu DATE.
Pozadie
Pri práci s údajmi z iného systému sa môžete dostať do situácie, keď program Excel správne nerozpozná dátumy, ktoré s nimi zaobchádzajú ako s textom. Môžete mať napríklad takéto textové hodnoty:
| Text | Zastúpený dátum |
| 20001024 | 24. októbra 2000 |
| 20050701 | 1. júla 20115 |
| 19980424 | 24. apríla 1998 |
| 28.02.2014 | 28.02.2014 |
Keď program Excel vyhodnotil hodnotu dátumu ako text, jednou z možností je použiť vzorec na analýzu textu na jeho komponenty (rok, mesiac, deň) a pomocou nich vytvoriť dátum pomocou funkcie DATE. Ako je uvedené vyššie, predtým, ako použijete vzorec, odporúčam najskôr vyskúšať nižšie uvedené riešenia (pridanie nuly a použitie textu do stĺpcov). Obe riešenia sú rýchlejšie a vyžadujú menšie úsilie.
Vysvetlenie
Funkcia DATE vytvorí platný dátum pomocou troch argumentov: rok, mesiac a deň:
=DATE(year,month,day)
V bunke C6 používame funkcie LEFT, MID a RIGHT na extrakciu každej z týchto zložiek z textového reťazca a výsledky vkladáme do funkcie DATE:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Funkcia LEFT extrahuje najviac 4 znaky za rok, funkcia MID extrahuje znaky na pozíciách 5-6 za mesiac a funkcia RIGHT extrahuje 2 dni úplne vpravo ako deň. Každý výsledok sa vráti priamo do funkcie DATE. Konečným výsledkom je správny dátum v programe Excel, ktorý je možné formátovať ľubovoľným spôsobom.
Tento prístup je možné podľa potreby prispôsobiť. Napríklad nerozpoznaný formát dátumu v riadku 8 je dd.mm.rrrr a vzorec v C8 je:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Dlhý text
Niekedy môžete mať dáta v dlhšej podobe, ako napríklad „11. apríla 2020 08:43:13“, ktoré Excel nerozpozná správne. V takom prípade môžete byť schopní upraviť reťazec spôsobom, ktorý umožňuje programu Excel správne rozpoznať dátum pomocou funkcie SUBSTITUTE. Nasledujúci vzorec nahrádza druhú inštanciu medzery („“) čiarkou a medzerou („,“):
=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month
Keď za názov mesiaca pridáme čiarku, Excel pochopí dátum, ale treba ho ešte trochu „nakopnúť“. Preto na konci pridáme nulu. Matematická operácia spôsobí, že sa Excel pokúsi previesť reťazec na číslo. Ak bude úspešný, bude to mať za následok platný dátum programu Excel. Na správne zobrazenie dátumu možno budete musieť použiť formátovanie čísla dátumu.
Bez vzorcov
Predtým, ako použijete vzorec na manuálnu analýzu a zostavenie dátumu z textu, vyskúšajte jednu z nasledujúcich opráv. Prvá možnosť využíva matematickú operáciu na to, aby trochu „štuchla“ do Excelu a prinútila ho, aby sa pokúsil vyhodnotiť text ako číslo. Pretože dátumy programu Excel sú v skutočnosti čísla, môže to byť často trik. Ak budú operácie úspešné, možno budete musieť použiť formát dátumu.
Ak chcete opraviť dátumy, pridajte nulu
Niekedy sa stretnete s dátumami v textovom formáte, ktorý by mal Excel rozpoznať. V takom prípade budete pravdepodobne môcť vynútiť Excel, aby prevádzal textové hodnoty na dátumy tak, že k hodnote pridáte nulu. Keď pridáte nulu, program Excel sa pokúsi previesť textové hodnoty na čísla. Keďže dátumy sú iba čísla, tento trik je skvelým spôsobom, ako previesť dátumy v textovom formáte, ktorému by program Excel mal skutočne rozumieť.
Ak chcete previesť dátumy na miesto pridaním nuly, vyskúšajte Prilepiť špeciálne:
- Do nepoužívanej bunky zadajte nulu (0) a skopírujte ju do schránky
- Vyberte problematické dátumy
- Prilepiť špeciálne> Hodnoty> Pridať
- Použiť formát dátumu (v prípade potreby)
Do vzorca, ako je tento, môžete tiež pridať nulu:
=A1+0
kde A1 obsahuje nerozpoznaný dátum.
Text do stĺpcov na opravu dátumov
Ďalším spôsobom, ako dosiahnuť, aby Excel rozpoznal dátumy, je použitie funkcie prevodu textu na stĺpce:
Vyberte stĺpec s dátumami a potom vyskúšajte Údaje> Text na stĺpce> Opravené> Dokončiť
Ak program Excel rozpozná dátumy, opraví ich všetky v jednom kroku.
