Excel zobrazuje data podle měsíce. Jak najít a zvýraznit nesprávnou hodnotu data a formát v Excelu

funkce DATE(). , anglická verze DATE(), in Vrátí celé číslo představující konkrétní datum. Vzorec =DATUM(2011;02;28) vrátí číslo 40602. Pokud byl před zadáním tohoto vzorce nastaven formát buňky na Obecný, bude výsledek formátován jako datum, tzn. 28.02.2011.

Syntaxe funkce

DATUM(rok;Měsíc;den)

Rok- argument, který může mít od jedné do čtyř číslic.
Měsíc je kladné nebo záporné celé číslo v rozsahu od 1 (leden) do 12 (prosinec) představující měsíc v roce.
Den je kladné nebo záporné celé číslo v rozsahu od 1 do 31 představující den v měsíci.

V EXCEL data jsou uloženy jako sekvence čísel (1, 2, 3, ...), což umožňuje provádět s nimi výpočty. Ve výchozím nastavení má 1. leden 1900 číslo 1 a 28. únor 2011 číslo 40602, protože interval mezi těmito daty je 40 602 dní. Chcete-li se dozvědět, jak EXCEL ukládá datum a čas, přečtěte si toto.

Příklady

Aby bylo možné doplnit datum 28.02.2011 obsažené v buňce A1 , například 5 let, můžete použít následující vzorec:
=DATUM(ROK(A1)+5,MĚSÍC(A1),DEN(A1))
výsledek dostaneme 28.02.2016

Chcete-li k datu 28. 2. 2011 přidat například 15 měsíců, můžete použít následující vzorec:
=DATUM(ROK(A1);MĚSÍC(A1)+15,DEN(A1)) nebo vzorec =DATEMES(A1;15)
výsledek dostaneme 28.05.2012

Poznámka. Při přidávání měsíců je rozdíl mezi DATEMES() a DATE(). K 30. 1. 2009 přidáme jeden měsíc:

=DATEMES("01/30/2009";1) se vrátí 28.02.2009, protože 30. únor neexistuje, pak funkce vrátí poslední den v měsíci, tzn. 28.02.2009 („nadbytečné“ 2 dny budou vyřazeny);
=DATE(YEAR("01/30/2009");MONTH("01/30/2009")+1;DAY("01/30/2009")) se vrátí 03/02/2009: k datu budou přidány „extra“ 2 dny (29. a 30. února).

Pokud napíšete vzorec =DATUM(2008;3;), vzorec vrátí 29.02.2008. Tito. vynecháním posledního argumentu Day jej nastavíme na 0. Proto se vzorec vrátí

Jeden z zajímavé funkce Microsoft Excel je DNES. Tento operátor se používá k zadání aktuálního data do buňky. Ale může být také použit s jinými formulemi v kombinaci. Podívejme se na hlavní vlastnosti funkce DNES, nuance jeho práce a interakce s ostatními operátory.

Funkce DNES vypíše datum nastavené v počítači do zadané buňky. Patří do skupiny operátorů "Datum a čas".

Musíte však pochopit, že tento vzorec sám o sobě neaktualizuje hodnoty v buňce. To znamená, že pokud program otevřete o pár dní později a nepřepočítáte v něm vzorce (ručně nebo automaticky), tak se v buňce nastaví stejné datum, a ne právě to aktuální.

Chcete-li zkontrolovat, zda je v konkrétním dokumentu nainstalován automatický přepočet, musíte provést řadu po sobě jdoucích akcí.

Nyní se jakákoli změna v dokumentu automaticky přepočítá.

Pokud z nějakého důvodu nechcete nastavit automatický přepočet, pak za účelem aktualizace obsahu buňky obsahující funkci pro aktuální datum DNES, musíte jej vybrat, umístit kurzor do řádku vzorců a stisknout tlačítko Vstupte.

V tomto případě, když je automatický přepočet zakázán, bude proveden pouze pro tuto buňku, nikoli pro celý dokument.

Metoda 1: Ruční zadání funkce

Tento operátor nemá žádný argument. Jeho syntaxe je poměrně jednoduchá a vypadá takto:

DNES()

Metoda 2: Použití Průvodce funkcí

Případně můžete použít Průvodce funkcí. Tato možnost je vhodná zejména pro začínající uživatele Excelu, kteří stále tápou v názvech funkcí a jejich syntaxi, i když v tomto případě je to maximálně jednoduché.

Metoda 3: Změna formátu buňky

Pokud před vstupem do funkce DNES Pokud měla buňka obecný formát, bude automaticky přeformátována na formát data. Ale pokud byl rozsah již naformátován pro jinou hodnotu, pak se nezmění, což znamená, že vzorec poskytne nesprávné výsledky.

Abyste viděli hodnotu formátu jednotlivé buňky nebo oblasti na listu, musíte vybrat požadovaný rozsah a na kartě „Domů“ se podívat, jaká hodnota je nastavena ve formuláři speciálního formátu na pásu karet v nástrojový blok "Číslo".

Pokud po zadání vzorce DNES Formát buňky nebyl automaticky nastaven "Datum", pak funkce zobrazí výsledky nesprávně. V tomto případě musíte změnit formát ručně.

Kromě toho můžete v okně formátování také změnit zobrazení dnešního data. Výchozí formát je šablona "dd.mm.yyyy". Zvýraznění různých možností hodnot v poli "Typ", který se nachází na pravé straně okna formátování, lze změnit vzhled zobrazení data v buňce. Po provedení změn nezapomeňte kliknout na tlačítko "OK".

Metoda 4: použití TODAY v kombinaci s jinými vzorci

Kromě toho funkce DNES lze použít jako součást složitých vzorců. V této funkci vám tento operátor umožňuje řešit mnohem širší problémy než při samostatném použití.

Operátor DNES Je velmi vhodné jej použít pro výpočet časových intervalů, například při uvádění věku osoby. Chcete-li to provést, napište do buňky výraz tohoto typu:

ROK(DNES())-1965

Chcete-li vzorec použít, klikněte na tlačítko ENTER.

Nyní v cele v správné nastavení přepočítávání vzorců dokladů bude neustále zobrazovat aktuální věk osoby narozené v roce 1965. Podobný výraz lze použít pro jakýkoli jiný rok narození nebo pro výpočet výročí události.

Existuje také vzorec, který zobrazuje hodnoty v buňce několik dní předem. Chcete-li například zobrazit datum za tři dny, vypadalo by to takto:

DNES()+3

Pokud potřebujete mít neustále na očích datum před třemi dny, vzorec bude vypadat takto:

DNES()-3

Pokud potřebujete v buňce zobrazit pouze číslo aktuálního dne v měsíci a ne celé datum, použije se následující výraz:

ZE DNE NA DEN())

Podobná operace pro zobrazení čísla aktuálního měsíce bude vypadat takto:

MĚSÍC(DNES())

Pokud buňky obsahují nesprávně formátované hodnoty, může to vést k nesprávným výpočtům ve vzorcích. Například místo typu hodnoty "Datum" buňka obsahuje typ hodnoty "Text". Při přípravě velkého množství dat byste měli zkontrolovat všechny typy hodnot, abyste se ujistili, že odpovídají konkrétnímu formátu. Tabulka byla například naplněna údaji z různých zdrojů, kde v jiná cesta datum bylo zaznamenáno. S takovou tabulkou nelze provádět různé výpočty. Je vizuálně obtížné postřehnout, kde bylo z pohledu programu Excel zadáno nesprávné datum ve špatném formátu.

Hledání textového formátu místo data v Excelu

Pro rychlé nalezení chybných hodnot v Excelu a zvýraznění všech buněk s nesprávným formátem použijeme podmíněné formátování. Vezměme si například jednoduchou tabulku:

Najděte a zvýrazněte buňky s nesprávným formátem zobrazení hodnoty:

Jak můžete vidět na obrázku, všechna data v textovém formátu jsou barevně zvýrazněna:

V podmínkách formátování jsme použili jednoduchou funkci =ETEXT(), která má pouze 1 argument – odkaz na kontrolovanou buňku. Adresa odkazu v argumentu funkce ITEXT musí být relativní, protože bude zkontrolována každá buňka ve vybraném rozsahu. Pokud aktuální kontrolovaná buňka obsahuje text (nikoli datum), jedná se o nesprávnou hodnotu aplikace Excel. Potom funkce ITEXT vrátí hodnotu TRUE a je okamžitě přiřazena k této buňce. nový formát(zelená výplň). Název funkce ITEXT je třeba číst jako zkratku dvou slov: If TEXT

Stejným způsobem čteme logické funkce, které lze použít ke kontrole jiných formátů a datových typů v buňkách:

ENETEXT – pokud ne text (funkce umožňuje také rychlé vyhledání data v excelovském textu);
ISNUMBER – pokud číslo (umožňuje rychle najít nesprávné číselné formáty v Excelu);
EOS – pokud dojde k chybě;
ERROR – pokud dojde k chybě;
IFERROR – pokud dojde k chybě (to není logická funkce, ale pro tento úkol je snadné jej optimalizovat);
EMPTY – pokud je prázdný;
ELOGIC – pokud jde o logickou hodnotu;
UND – pokud hodnota není k dispozici (#N/A);
NEUSTÁ – pokud je hodnota lichá;
EVEN – pokud je hodnota sudá;
LINK – pokud jde o odkaz;
FORMULA – pokud vzorec.

Pokud chcete, můžete experimentálně vyzkoušet všechny funkce v akci.