Formaty daty w arkuszach i problemy z formułami obsługującymi daty

Rozpoznawanie czy komórka zawiera datę czy tekst

Jeśli mamy kłopot z działaniami na datach, warto na początku upewnić się, że pracujemy z formatem daty, a nie z formatem tekstowym.

Domyślnie arkusz wyrównuje do prawej wszystkie komórki zawierające daty, a do lewej tekst. To jest najszybszy sposób sprawdzenia. Jeśli jednak arkusz jest mocno sformatowany, należy użyć formuły ISDATE(), która zwraca wartość prawda / fałsz w zależności od tego czy w nawiasach wstawiono coś co można przeczytać jako datę czy coś innego.

Widzimy tu zastosowanie formuły ISDATE do testowania zawartości arkusza. 17.1 nie zostało odczytane jako 17 stycznia.

Wpisywanie daty wewnątrz formuł

Wiele formuł arkuszy jest w stanie zorientować się, że wpisujemy datę, jednak jeśli ma się pojawić wewnątrz formuły, musi być zawarta w cudzysłowach.

Przykład najprostszy:

To co widzimy w komórce to data zapisana w wewn. formacie arkuszy. Żeby zobaczyć ją w czytelniejszej formie wystarczy zmienić format.

W efekcie dostajemy czytelną datę:

Przykład z formułą:

=randbetween("2021-01-01";"2021-05-20")

losuje datę od z zakresu od pierwszego do 20 maja 2021

Kolejny przykład tworzy listę 10 dat, z których pierwsza to 1 stycznia. Kolejne daty następują po sobie w odstępach 29 dniowych.

Jak arkusz widzi datę i czas “od środka”

Arkusz widzi czas i datę jako liczbę i może na nich wykonywać działania. Data początkowa dla niego to 31 grudnia 1899.

Jeśli weźmiemy jedynkę i sformatujemy jako datę i czas, uzyskamy powyższą wartość

Jeśli do tej wartości dodamy 1, uzyskamy 1 stycznia 1900 roku. A jeśli dodamy 44332, uzyskamy 21 maja 2021 roku, czyli datę powstania tego wpisu. Istotne jest to, że również jeśli wyświetlamy np. tylko godziny, dodanie wartości 1 zmieni nam datę o 1 dzień, a nie np. godzinę czy minutę.

Jeśli chcemy dodać godzinę, musimy do danej wartości dodać 1/24, a żeby dodać minutę 1/1440, sekundę 1/86400.

Jeśli w komórce lub komunikacie błędu widzisz bezsensowną liczbę 44234 (czterdzieści cztery tysiące z hakiem) to śmiało zakładaj, że jest to data w wewnętrznym formacie arkusza.
Nostradamus

Data wewnątrz formuły pisana wprost

Jeśli chcemy odwołać się do daty i napiszemy ją w formule wprost – np. jako “2021-05-21” i porównamy z datą w jakiejś komórce, arkusz uzna te wartości za różne.

Widzimy tu, że test czy B8 jest równy “2021-05-21” daje rezultat negatywny.

To samo wydarzy się, gdy umieścimy datę w ten sposób w formule IF, VLOOKUP czy innej. Dzieje się to dlatego, że w kolumnie B mamy datę, którą arkusz widzi w swoim wnętrzu jako 44333, a w formule pojawia się ciąg znaków, który jest czytany jako tekst.

Możemy ten problem rozwiązać na dwa sposoby:

Zmiana daty na tekst i użycie w formule

Formuła TO_TEXT() przekształciła zawartość komórki B9 w tekst, dzięki czemu arkusz uznaje, że jest to to samo co “2021-05-21”

Zmiana tekstu na datę i użycie w formule

Formuła DATEVALUE() przekształciła ciąg tekstowy w datę, dzięki temu również uzyskujemy tożsamość. Arkusz “pod pokrywką” widzi to jako sprawdzenie czy 44333 = 44333 i daje rezultat TRUE (PRAWDA).

Jak poradzić sobie z datami zawierającymi nazwy miesięcy w innym języku

Zdarza się, że wklejamy dane z innego źródła i zawierają one dane (np. znaczniki czasu) w formacie z nazwami słownymi: 23-Apr-2017 11:42:35.883.

Widać, że miesiąc jest po angielsku (April, czyli Kwiecień) i jeśli nasz arkusz jest ustawiony na język polski, to data zostanie odczytana jako tekst. Najszybciej możemy temu zaradzić zmieniając na chwilę ustawienia arkusza na język angielski.

Następnym krokiem jest zmiana formatu daty na taki, który nie zawiera miesięcy lub dni tygodnia słownie. Na przykład korzystając z domyślnych formatów daty w menu:

Po tych operacjach należy odświeżyć okno przeglądarki. Jeśli tego nie zrobimy, na ekranie mogą się pojawić sprzeczne informacje.

Użycie dat wewnątrz formuły QUERY

Jeśli porównujemy daty wewnątrz formuły QUERY, musimy dodać przedrostek date, do wpisywanej wartości jak na załączonym przykładzie wyświetlającym zawartość kolumny C, jeśli data (B) jest późniejsza niż 16 maja 2021.

=query(B20:C30;"select C where B > date '2021-05-16'")

Jeśli data ma znajdować poza formułą QUERY, musimy się trochę nagimnastykować. Trzeba pamiętać, że arkusz przechowuje “pod pokrywką” datę w postaci liczby, zatem jeśli w query odwołamy się wprost do zawartości komórki i porównamy ją z zawartością w kolumnie wewnątrz QUERY, to dostaniemy komunikat błędu.

Żeby tego uniknąć, należy do QUERY wciągnąć datę przekstałconą w tekst o odpwiednim formacie, a następnie ponownie wytłumaczyć, że chodzi o datę – tym razem już w składni QUERY. Trochę to zagmatwane, wynika bowiem z tego, że wewnątrz formuły QUERY używamy odrębnego języka.

=query(B20:C30;"select C where B > date '"&text(F21;"yyyy-mm-dd")&"'")

"&treść&" – mówi o tym, że wychodzimy na moment ze składni Query, generujemy treść przy pomocy standardowych formuł i zaraz wracamy do wyrażenia QUERY z wynikiem tej formuły.

text(F21;"yyyy-mm-dd") – oznacza, że bierzemy zawartość komórki i przekształcamy w tekst według odpowiedniego wzrorca formatowania.

Jeśli potrzebujesz więcej informacji na temat operacji na datach – funkcji przygotowanych pod obsługę kalendarza – polecam wpis: Jak określać dziś, jutro, koniec miesiąca, liczbę dni do wydarzenia, etc .