Excel ukládá hodnoty data a času jako sériová čísla do back -endu.
To znamená, že ačkoli se v buňce může zobrazit datum jako „10. ledna 2021-2022“ nebo „01/10/2020“, v back-endu to Excel ukládá jako číslo. To je užitečné, protože také umožňuje uživatelům snadno přidat/odečíst datum a čas v aplikaci Excel.
V aplikaci Microsoft Excel pro Windows je „01 led 1900“ uložen jako 1, „02 led 1900“ je uložen jako 2 atd.
Níže mají oba sloupce stejná čísla, ale ve sloupci A jsou čísla a ve sloupci B datum, které je tímto číslem reprezentováno.
To je také důvod, proč někdy můžete očekávat datum v buňce, ale nakonec uvidíte číslo. Vidím, že se to děje neustále, když stahuji data z databází nebo webových nástrojů.
Stává se to, když je formát buňky nastaven tak, aby místo data zobrazoval číslo jako číslo.
Jak tedy můžeme? převést tato sériová čísla na data?
O tom je tento návod.
V tomto tutoriálu bych vám ukázal dva opravdu snadné způsoby převodu sériových čísel na data v Excelu
Poznámka: Excel pro Windows používá datový systém 1900 (což znamená, že 1 by představovalo 1. leden 1900, zatímco Excel pro Mac používá datový systém 1904 (což znamená, že 1 by představovalo 1. ledna 1904 v Excelu na Macu)Začněme tedy!
Převod sériových čísel na data pomocí formátování čísel
Nejjednodušší způsob, jak převést sériové číslo data na datum, je změnit formátování buněk, které tato čísla mají.
Některé z běžně používaných formátů data najdete na kartě Domů na pásu karet
Ukážu vám, jak to udělat.
Použití vestavěných možností formátu data na pásu karet
Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete převést všechna tato čísla ve sloupci A na odpovídající datum, které představuje.
Níže jsou uvedeny kroky, jak to provést:
- Vyberte buňky s číslem, které chcete převést na datum
- Klikněte na kartu „Domů“
- Ve skupině „Číslo“ klikněte na rozevírací nabídku Formátování čísla
- V rozevíracím seznamu vyberte možnost „Dlouhé datum“ nebo Krátké datum (podle toho, v jakém formátu chcete, aby tato čísla byla)
A je to!
Výše uvedené kroky by převedly čísla do vybraného formátu data.
Výše uvedené kroky nezměnily hodnotu v buňce, pouze způsob jejího zobrazení.
Všimněte si toho, že Excel přebírá krátké formátování data na základě regionálního nastavení vašeho systému. Pokud jste například v USA, formát data by byl MM/DD/RRRR a pokud jste ve Velké Británii, formát data by byl DD/MM/RRRR.
Jedná se o rychlý způsob převodu sériových čísel na data, ale má několik omezení:
- Existují pouze dva formáty data - krátké datum a dlouhé datum (a to také v konkrétním formátu). Pokud tedy chcete získat pouze měsíc a rok, nikoli den, pomocí těchto možností to nebudete moci udělat.
- Pomocí možností formátování v rozevíracím seznamu nelze zobrazit datum ani čas. Můžete buď zvolit zobrazení data nebo času, ale ne obojí.
Pokud tedy potřebujete větší flexibilitu ve způsobu, jakým chcete zobrazovat data v aplikaci Excel, musíte použít možnost Vlastní formátování čísel (popsaná dále v tomto kurzu).
Někdy při převodu čísla na datum v aplikaci Excel se místo data mohou zobrazit nějaké znaky hash (něco jako #####). k tomu dochází, když šířka sloupce nestačí k přizpůsobení celého data. V takovém případě stačí zvětšit šířku sloupce.Vytvoření vlastního formátu data pomocí dialogového okna Formátování čísla
Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete převést sériová čísla ve sloupci A na data v následujícím formátu - 01.01.2020
Všimněte si, že tato metoda není ve výchozím nastavení k dispozici v metodě pásu karet, kterou jsme probrali dříve.
Níže jsou uvedeny kroky, jak to provést:
- Vyberte buňky s číslem, které chcete převést na datum
- Klikněte na kartu „Domů“
- Ve skupině Čísla klikněte na ikonu spouštěče dialogového okna (je to malá nakloněná šipka v pravé dolní části skupiny)
- V dialogovém okně „Formát buněk“, které se otevře, zkontrolujte, zda je vybrána karta „Číslo“
- V možnostech „Kategorie“ vlevo vyberte „Datum“
- V poli „Typ“ vyberte požadované formátování.
- Klikněte na OK
Výše uvedené kroky by převedly čísla do vybraného formátu data.
Jak vidíte, v tomto případě existuje více možností formátování data (ve srovnání s krátkým a dlouhým datem, které jsme dostali s pásem karet).
A pokud nenajdete hledaný formát, můžete si také vytvořit vlastní formát data.
Řekněme například, že chci zobrazit datum v následujícím formátu - 01/01/2020 (což v dialogovém okně buněk formátu již není možnost).
Zde je návod, jak to mohu udělat:
- Vyberte buňky s čísly, která chcete převést na datum
- Klikněte na kartu Domů
- Ve skupině Čísla klikněte na ikonu spouštěče dialogového okna (je to malá šipka naklonění v pravé dolní části skupiny)
- V dialogovém okně „Formát buněk“, které se otevře, zkontrolujte, zda je vybrána karta „Číslo“
- V možnostech „Kategorie“ vlevo vyberte „Vlastní“
- Do pole vpravo zadejte
mm/dd/rrrr
- Klikněte na OK
Výše uvedené kroky by změnily všechna čísla do zadaného formátu čísel.
S vlastním formátem získáte plnou kontrolu a umožňuje vám zobrazit datum v libovolném formátu. Můžete také vytvořit formát, ve kterém se ve stejné buňce zobrazuje datum a čas.
Například ve stejném příkladu, pokud chcete zobrazit datum i čas, můžete použít následující formát:
mm/dd/rrrr hh: mm dop./odp
Níže je tabulka, která ukazuje kódy formátu data, které můžete použít:
| Kód formátování data | Jak formátuje datum |
| m | Zobrazuje měsíc jako číslo od 1 do 12 |
| mm | Ukažte měsíce jako dvouciferná čísla od 1. do 12. dne |
| mmm | Ukazuje měsíc jako tři písmena jako v lednu a prosinci |
| mmmm | Zobrazuje úplný název měsíce jako leden-prosinec |
| mmmmm | Zobrazuje první abecedu názvu měsíce jako v J-D |
| d | Zobrazuje dny jako 1-31 |
| dd | Zobrazuje dny jako 01-31 |
| ddd | Zobrazuje dny jako ne-so |
| dddd | Zobrazuje dny jako neděle-sobota |
| yy | Ukazuje roky jako 00-99 |
| rrrr | Ukazuje roky jako 1900-9999 |
Převod sériových čísel na data pomocí TEXTOVÉHO vzorce
Metody popsané výše fungují změnou formátování buněk, které mají čísla.
V některých případech však tyto metody nebudete moci použít.
Například níže mám soubor dat, kde čísla mají vedoucí apostrof - který převádí tato čísla na text.
Pokud se pokusíte pomocí vestavěných možností změnit formátování buněk s touto sadou dat, nic by to neudělalo. Důvodem je, že je Excel nepovažuje za čísla, a protože data pro čísla Excel odmítá vaše přání je formátovat.
V takovém případě můžete buď převést tento text na čísla a poté použít výše uvedené metody formátování, nebo použít jiný způsob převodu sériových čísel na data - pomocí funkce TEXT.
Funkce TEXT přebírá dva argumenty - číslo, které je třeba naformátovat, a formátovací kód.
Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete převést všechna čísla a sloupec A na data.
Pod vzorcem, který by to mohl udělat:
= TEXT (A2, "MM/DD/RRRR")
Všimněte si, že ve vzorci jsem určil formátování data ve formátu MM/DD/RRRR. Pokud potřebujete použít jakékoli jiné formátování, můžete pro něj zadat kód jako druhý argument vzorce TEXT.
Tento vzorec můžete také použít k zobrazení data a času.
Pokud například chcete, aby byl konečný výsledek ve formátu - 01/01/2020 0:00, použijte následující vzorec:
= TEXT (A2, "mm/dd/rrrr hh: mm AM/PM")
Ve výše uvedeném vzorci jsem také přidal formát času, takže pokud jsou v číslech desetinné části, zobrazí se jako čas v hodinách a minutách.
Pokud chcete pouze datum (a nikoli základní vzorec), převeďte vzorce na statické hodnoty pomocí vložení speciálního.
Jednou z velkých výhod použití funkce TEXT k převodu sériových čísel na data je, že můžete výsledek vzorce TEXT kombinovat s jinými vzorci. Výsledné datum můžete například zkombinovat s textem a ukázat něco jako - Termín splatnosti je 01. ledna 2020 nebo Uzávěrka je 1. ledna 2020
Poznámka: Pamatujte, že data a čas jsou uloženy jako čísla, kde každé celé číslo představuje jeden celý den a desetinná část by představovala tolik části dne. Takže 1 by znamenalo 1 celý den a 0,5 by znamenalo půl dne nebo 12 hodin.
Toto jsou tedy dva jednoduché způsoby, které můžete použít k převodu sériových čísel na data v aplikaci Microsoft Excel.
Doufám, že jste našli tento návod užitečný!
