Jeżeli data w Excelu sprawiła Ci kiedykolwiek problem, to ten artykuł jest właśnie dla Ciebie. Dowiesz się z niego dlaczego Excel nie zawsze uznaje Twoją datę za datę. Zobaczysz jak łatwe może być odejmowanie dat od siebie. Na koniec przekonasz się, że w Excelu jest data, której w rzeczywistości nie było. Gotowi?

Czym jest data w Excelu?

Musisz wiedzieć, że Excel traktuje datę jak liczbę. Konkretnie jest to liczba dni, która upłynęła od fikcyjnej daty 0 stycznia 1900 roku. Wpisując liczbę 44019 w komórkę sformatowaną jako data, po wciśnięciu Entera wyświetli się nam dzisiejsza data, czyli 7 lipca 2020 roku. Dokładnie tyle dni upłynęło od pierwszej interpretowanej przez Excel daty. Ostatnia uznawana przez program data to 31 grudnia 9999 roku.

Wszystkie daty przed i po okresie od 1900-01-01 do 9999-12-31 są traktowane przez Excela jako tekst.

Formaty daty w Excelu

Excel (w większości przypadków) automatycznie wykryje, że wprowadzony przez Ciebie ciąg powinien być datą. Jeżeli tego nie zrobi to wpisana przez Ciebie wartość zostanie wyrównana do lewej strony komórki. W Excelu jest to jasny sygnał, że wprowadzone dane są uważane za tekst.

Niemniej chcemy, żeby nasza wartość była datą, zamiast tekstem. W takim wypadku trzeba odpowiednio sformatować naszą liczbę na datę. Klikamy na komórkę, w której chcemy zmienić formatowanie i używamy skrótu CTRL + 1. W oknie, które nam się pojawi wybieramy „Data” i zaznaczamy interesujący nas format daty.

Okno formatowania komórek z wyborem formatu daty w Excelu
Formaty daty w Excelu

Przydatne skróty klawiaturowe

Jeżeli chcesz odwrócić formatowanie daty na formatowanie ogólne użyj skrótu: CTRL+SHIFT+# na komórce z datą.

Skrót CTRL+SHIFT+~ ponownie sformatuje zaznaczoną komórkę na wcześniej wybrany format daty.

Ustawienia regionalne w Excelu

Zgodnie z powyższą grafiką dwa pierwsze typy daty w Excelu są oznaczone gwiazdką (*). Ten typ jest uzależniony od ustawień systemowych komputera, na którym pracujesz. Na moim komputerze data, która jest wyświetlana w prawym dolnym rogu jest w formacie dd.mm.rrrr . W związku z powyższym, Excel w moim przypadku traktuje taki format daty jako domyślny i zgodny z moimi ustawieniami regionalnymi.

W przypadku chęci zmiany ustawień systemowych dotyczących wyświetlania daty lub czasu w Windows 10, należy przejść do Panelu sterowania, następnie wybrać „Zegar i region” oraz „Zmień formaty daty, godziny lub liczb”.

Jeśli format daty ma nie być zależny od ustawień regionalnych komputera, na którym będzie wyświetlony skoroszyt, należy wybrać format daty bez gwiazdki.

Funkcja DATA

W wielu przypadkach praktyczniej jest zapisać datę przy użyciu funkcji DATA. Funkcja ta przyjmuje trzy parametry ROK, MIESIĄC, DZIEŃ – dokładnie w tej kolejności. Gdzie ROK zwraca liczbę z przedziału od 1900 do 9999, MIESIĄC zwraca liczbę od 1 do 12, a DZIEŃ zwraca liczbę od 1 do 31.

Aby zapisać przykładowo dzisiejszą datę przy użyciu funkcji, należy w komórce arkusza wpisać =DATA(2020;07;07) i zatwierdzić Enterem. Excel wyświetli datę z wykorzystaniem funkcji zgodnie z wybranym przez nas typem formatowania daty.

Jeżeli posiadamy informację o roku, miesiącu i dniu w trzech osobnych komórkach, przy pomocy funkcji DATA możemy scalić te dane w jedną datę.

Wykorzystanie funkcji DATA przy latach, miesiącach i dniach w 3 różnych komórkach.
Wykorzystanie funkcji DATA
Wyświetlenie funkcji DATA, które nie różni się od ustalonego formatowania.
Wyświetlenie funkcji DATA

Funkcja DZIŚ i TERAZ

W programie Excel jest możliwość wstawienia aktualnej daty lub godziny w sposób statyczny. Służy do tego skrót CTRL + ;, który wstawia nam aktualną datę oraz skrót CTRL + SHIFT + ;, który wstawia nam aktualną godzinę. Wprowadzone w ten sposób dane nie będą aktualizowane przy ponownym przeliczeniu arkusza.

Oprócz wartości statycznych jest możliwość wprowadzenia daty i godziny w sposób dynamiczny. W takim wypadku musimy wykorzystać bezparametrową funkcję =DZIŚ(). Po jej użyciu w komórce pojawi się aktualna data, która przy ponownym przeliczeniu arkusza będzie się automatycznie aktualizowała. Analogicznie działa bezparametrowa funkcja =TERAZ(), która zwróci nam aktualną datę oraz godziną. Informacje dla tych funkcji są pobierane z zegara systemowego.

Dodawanie i odejmowanie dat w Excelu

W programie Excel możemy skorzystać z dodawania i odejmowania lat, miesięcy oraz/lub dni od naszej daty. Zobaczmy to w praktyce na przykładzie naszej tabeli z datami. Korzystając z funkcji DATA, do roku dodamy liczbę 10. Wynikiem będzie data, która będzie odpowiadała 10 rocznicy naszej daty początkowej. Ogólny zapis funkcji będzie wyglądał następująco: =DATA(ROK+10;MIESIĄC;DZIEŃ). Na zdjęciu poniżej pokazane jest zastosowanie formuły w praktyce.

Przykład dodawania lat w funkcji DATA

Analogicznie przy odejmowaniu zastosujemy odjęcie danej liczby od roku, miesiąca lub dnia. Przykładowo chcemy zobaczyć jaka byłaby data tydzień przed naszą datą początkową. Od pozycji DZIEŃ odejmujemy 7.

Przykład odejmowania dni w funkcji DATA
Przykład odejmowania dni w funkcji DATA

Funkcja DATA.RÓŻNICA

Jeżeli chcesz wyrazić dokładną różnicę pomiędzy datami z jednoczesnym uwzględnieniem lat, miesięcy i dni to potrzebujesz ukrytej funkcji DATA.RÓŻNICA. Wspomnianej funkcji nie znajdziesz na wstążce Formuły w Excelu. Dlaczego tak jest? DATA.RÓŻNICA powstała dla zachowania kompatybilności z innym arkuszem kalkulacyjnym Lotus 1-2-3, a sama funkcja może w określonych sytuacjach pokazywać niepoprawne wyniki.

Funkcja DATA.RÓŻNICA przyjmuje 3 parametry: datę początkową, datę końcową i jednostkę czasu.

Datę początkową i datę końcową można wprowadzić przy użyciu funkcji DATA, w cudzysłowie jako łańcuch znaków („2020-07-07”) lub jako liczba dni, która upłynęła od daty zero w Excelu.

Jednostki czasu są podzielone na sześć różnych interwałów czasowych. Wszystkie zostały wyszczególnione w tabeli poniżej.

jednostkaopis
Yzwraca liczbę pełnych lat w podanym okresie
Mzwraca liczbę pełnych miesięcy w podanym okresie
Dzwraca liczbę pełnych dni w podanym okresie
YMzwraca różnicę między miesiącami daty początkowej i końcowej, ignoruje dni i lata dat
YDzwraca różnicę między dniami daty początkowej i końcowej, ignoruje lata dat
MDzwraca różnicę między dniami daty początkowej i końcowej, ignoruje miesiące i lata dat
Jednostki czasu w funkcji DATA.RÓŻNICA

Funkcja DATA.RÓŻNICA w praktyce

Funkcja jest bardzo przydatna przy obliczaniu wieku. Załóżmy, że daty, które mieliśmy w tabeli są datami urodzin. Chcemy policzyć ile lat obecnie mają osoby urodzone we wskazanej dacie. Tworzymy nową kolumnę, w której użyjemy formuły: =DATA.RÓŻNICA(DATA POCZĄTKOWA;DZIŚ();"Y").

Funkcja DATA.RÓŻNICA w praktyce
Funkcja DATA.RÓŻNICA w praktyce

Jeżeli potrzebujemy dokładniejszego wieku musimy wzbogacić funkcję o różnicę w miesiącach i dniach. Dla lepszego zapisu dodamy informację czego liczbę wyświetlamy. Po znaku „&” możemy dodawać kolejne elementy formuły. Tekst, który chcemy wyświetlić umieszczamy w cudzysłowie. W związku z tym do wcześniej napisanej formuły dopisujemy &" lat" .

Następnie dodajemy kolejne dwie funkcję wraz z ich opisami, czyli: &DATA.RÓŻNICA(DATA POCZĄTKOWA;DZIŚ();"YM")&" miesięcy "&DATA.RÓŻNICA(DATA POCZĄTKOWA;DZIŚ();"MD")&" dni". Na zdjęciu poniżej kompletny zapis formuły.

Wiek w latach, miesiącach i dniach.
Aktualny wiek przedstawiony w latach, miesiącach i dniach

W wierszu 5 na powyższym zdjęciu pojawiają nam się brzydko wyglądające zera. Możemy je wyeliminować używając funkcji JEŻELI, o której przeczytasz w artykule pod tym linkiem.

Ciekawostki o datach w Excelu

Na zakończenie, obiecana na wstępie ciekawostka o dacie, która występuje w Excelu, a której w rzeczywistości nie było. Otóż w programie Excel rok 1900 jest rokiem przestępnym. Zgodnie z rachubą stosowaną dla lat przestępnych, numeracja roku przestępnego musi być:

  • podzielna przez 4, ale nie podzielna przez 100
  • podzielna przez 400

Po szczegóły odsyłam do Wikipedii.

Programiści Excela błędnie zaliczyli rok 1900 jako rok przestępny. Nie spełnia on wymogów rachuby dla tego typu lat. Program uwzględnia zapis 29.02.1900 jako datę (a nie tekst). Dodatkowo,wyświetli ją również przy auto uzupełnieniu komórek kolejnymi datami.

Błąd nie został naprawiony ze względu na zbyt wiele potencjalnych i realnych problemów, które pojawiłyby się po jego wyeliminowaniu. Lista strat, które powstałyby po naprawie błędu kilkakrotnie przekraczała listę zysków wynikających z jego naprawy.

Dwa systemy dat w Excelu

W związku z powyższym, dużo łatwiej było wprowadzić drugi model obliczania dat. Dla wczesnych komputerów Macintosh został opracowany system dat z 1904 r. Różnica między systemem dat z 1900 roku, a dat z 1904 roku wynosi dokładnie 1 462 dni, czyli cztery lata i jeden dzień. W przeszłości Excel domyślnie używał dla skoroszytów Macintosh dat z systemu z 1904 roku. Aktualnie wspomniane skoroszyty korzystają domyślnie z systemu z błędem roku przestępnego, a konwersja dat nie jest potrzebna.

Jako ciekawostkę dodam, że w najnowszym Excelu dalej możemy ustawić drugi system dat. Aby go wprowadzić w danym skoroszycie należy przejść do zakładki „Plik”, następnie wybieramy „Opcję”. W oknie, które nam się otworzy z lewej strony wybieramy „Zaawansowane”, a po prawej stronie suwakiem szukamy opcji „Podczas obliczania w tym skoroszycie …” i zaznaczamy checkbox „Użyj systemu 1904”. Zmiany akceptujemy przyciskiem „OK”.

Jeżeli w arkuszu były już wprowadzone daty zostaną one przesunięte o 4 lata i jeden dzień. Obecnie nie ma logicznego argumentu dla powszechnego stosowania równolegle drugiego systemu dat.


Jeżeli ten tekst był dla Ciebie przydatny podziel się nim ze znajomymi. To jest najlepszy sposób na docenienie Autorki tekstu 😉 Jeśli masz pytania do tego artykułu zadaj je w komentarzu pod postem.