B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 1

Data i czas w Excelu Wstęp

Celem artykułu jest omówienie sposobu w jaki projektanci Excela poradzili sobie z pojęciami daty i czasu w arkuszu kalkulacyjnym, czyli w jaki sposób w Excelu ujęto pojęcia daty i czasu, aby zabezpieczyć ich logiczną funkcjonalność, głównie na potrzeby działalności gospodarczej.

Należy zauważyć, że z przyczyn historycznych i kulturowych sposób przedstawiania daty jest różny w różnych narodowościach i w różnych miejscach na kuli ziemskiej. Przykładowo Amerykanin zapytany o to, kiedy w tym roku przypada jego święto narodowe odpowie, że w dniu „lipiec czwarty 2013 roku”. Konsekwentnie zatem w dokumentach, czyli również dokumentach elektronicznych, zapisze on taką datę jako 7/4/13. Tymczasem pozostałe nacje (używające komputerów ) raczej przeczytałyby ten zapis jako „siódmy kwietnia 2013” roku. A może chodziło o siódmy kwietnia 1913 roku? Niestety nie jest to jedyny przypadek niejednoznaczności interpretacji zapisu daty przez różne narodowości, podobnych problemów jest więcej.

Jak łatwo się domyślić taka występująca powszechnie różnorodność zapisu i prezentacji daty może powodować potencjalnie mnóstwo nieporozumień, często o katastrofalnych w skutkach efektach. Jako że coraz częściej pracujemy w środowiskach międzynarodowych, importujemy (czy też po prostu wklejamy) do Excela dane z przeróżnych źródeł, w których sposób zapisu daty może być niezgodny z tym, jaki mamy ustawiony w naszym Excelu, powstaje pytanie jak sobie z tym problemem poradzić? Mam nadzieję, że poniższy artykuł pomoże w jego rozwiązaniu. Założenia – 4 główne założenia dla daty i czasu w Excelu U podstawy wszystkich założeń, w jaki sposób w Excelu potraktować datę i czas, legło założenie, że w działalności gospodarczej istnieje potrzeba wykonywania obliczeń matematycznych na jednostkach daty i czasu. Przykładowo: termin zapłaty za 14 dni, staż pracy 5 lat, wydajność maszyny 200 szt./minutę itp. Czyli dodajemy, odejmujemy, dzielimy.

Jeżeli zatem istnieje potrzeba wykonywania działań matematycznych, to w Excelu data i czas muszą być przedstawione jako LICZBY. Konsekwencją tego założenia są dwa kolejne. Musiano zdefiniować gdzie jest zero (jaka data to zero), czyli od kiedy zaczynamy liczyć daty oraz ile wynosi jednostka obliczeniowa, czyli ile to jest jeden? Ostatnim założeniem było przyjęcie, że data i czas nie mogą przyjmować wartości ujemnych. Zestawiając 4 założenia mamy:

Data i czas w Excelu są liczbami, Jednostką obliczeniową jest jeden dzień, Zaczynamy liczyć od początku dnia pierwszego stycznia 1900 roku, Data i czas są większe od zera.

Konsekwencją tego ostatniego założenia jest niemożliwość wykonywania obliczeń na

datach przed 1900-01-01. Wiedza ogólna określa nam relację pomiędzy datą i czasem jako następującą: jeden

dzień ma 24 godziny . Skoro tak, to jedna godzina w Excelu to liczba niewymierna

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 2

wynikająca z wykonania działania =1/24. Konsekwentnie dalej można określać (obliczać) minuty, sekundy i ich części.

Ustawienia systemowe dla daty Wspomniano powyżej, że „co kraj to obyczaj”, czyli, że większość nacji potrzebuje innej formy prezentacji daty, żeby ją poprawnie odczytać. Przy okazji napiszmy, że problem ten dotyczy nie tylko dat, ale również na przykład separatora liczb dziesiętnych. W polskim Excelu mamy ustawiony domyślnie jako przecinek, w wersjach anglojęzycznych jest nim kropka. I tutaj, w przypadku daty, otwiera się ocean możliwych kombinacji, nad którymi nie wiem już kto i jak chce zapanować? Jako przykład pokazano poniżej próbę zmiany wyświetlanej daty w zależności od wybranego ustawienia regionalnego (Excel 2007PL, opcja formatowanie komórek). Zapewniam Państwa, że ta sama operacja wykonana na wersji Excel 2007ENG wyświetliłaby już inne separatory daty i nie byłby to minus.

Prowadząc szkolenia i zaglądając kursantom do ich komputerów, spotkałem się z przeróżnymi formatami wyświetlanych dat, zwłaszcza w dużych korporacjach. Bałagan w tej kwestii jest ogromny a spowodowany jest głównie próbami ujednolicenia formatów. Przykładowo na komputerze zainstalowany jest polski Excel, system operacyjny po angielsku (amerykański), język wyświetlania w Excelu ustawiony na angielski (Wielka Brytania) i do tego wyświetlanie daty po niemiecku (bo centrala firmy w Niemczech), uff, można zwariować, prawda

Napiszę tak- dla własnego dobra proszę starać się nie kombinować. Tam, gdzie to tylko możliwe, należy wg mnie korzystać z ustawień domyślnych pobieranych z systemu operacyjnego. Unika się w takich przypadkach potencjalnych nieporozumień.

Przykładowo przy otwieraniu pliku Excela przez innego użytkownika, który posiada Excela ustawionego w innej niż nasza wersji językowej, Excel sam dokona „przetłumaczenia” dat (i nie tylko dat, nazw funkcji itp również) na format taki, jaki obowiązuje na jego komputerze. Co to jest za format? Otóż wersja językowa dat zakodowana jest poprzez tak zwany kod regionu (Language Code ID). Przykładowo polski region, dla wersji Excela 2003 – 2010 ma datę zakodowaną jako [$-415] i można używać tego kodu w formatowaniu

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 3

niestandardowym. Po wpisaniu kodu regionu należy za nim dopisać symbol lub kombinację symboli dla lat, miesięcy lub dni według przykładów opisanych w dalszej części artykułu. Amerykańskie daty a dokładniej English (United States) to kod [$-409]. Dla ciekawskich kod dla Chin to [$-804]. Przykład zastosowania tych kodów w arkuszu pokazano na fotce poniżej.

Cechami charakterystycznymi różnicującymi pomiędzy sobą wersje językowe daty w

Excelu są kolejność znaków opisujących rok, miesiąc i dzień ilość zastosowanych znaków, domyślnie liczb (od 1 do 4) rodzaj separatora oddzielający znaki w dacie

I tak -w polskim Excelu domyślnie poprawny format daty (dla wpisów z klawiatury)

to rrrr-mm-dd, czyli na przykład 2013-01-25. Jak wspomniano na wstępie w wersji amerykańskiej ta sama data powinna być wprowadzona z klawiatury jako mm/dd/rr (01/25/2013) natomiast w wersji niemieckiej jako 25.01.2012. Jeżeli jest to u Państwa inaczej to spokojnie, oczywiście nie ma problemu, wszystko zależy od ustawień.

Jak zatem najszybciej sprawdzić na jaki format mam ustawioną datę. Należy aktywować niesformatowaną komórkę i wcisnąć Ctrl + ; (klawisze „Ctrl” i „średnik”). Spowoduje to wstawienie do tej komórki bieżącej (pobieranej z systemu operacyjnego) daty w ustawionym jako domyślny formacie.

Skoro format wyświetlenia może być różny, to jaką wartość przechowuje Excel? Przecież tą samą datę może prezentować na różnych komputerach w różny sposób. To jest zasadnicze pytanie i jeżeli dobrze zapamiętacie Państwo odpowiedź na nie, nie powinniście mieć więcej kłopotów ze zrozumieniem operacji na datach w Excelu. Otóż bez względu na to, co nam się wyświetla w komórce, Excel przechowuje w swojej pamięci datę jako liczbę.

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 4

Na jednej z fotek powyżej mamy w komórce A5 wyświetloną liczbę 41 281. Taką samą wartość mają wpisy do komórek A1:A4, natomiast tylko formatem, wyświetlono (pokazano) te liczby inaczej, jako zrozumiałe dla nas ludzi daty. Innymi słowy, na 99,9% polski użytkownik Excela poprawnie zrozumie zapis 2012-07-04. W ten sam sposób Amerykanin zrozumie zapis 07/04/13 natomiast Anglik zinterpretuje zapis 04/07/2013. We wszystkich tych przypadkach „osobnik komputer” do prawidłowego „zrozumienia” tych dat potrzebuje liczby i tylko liczbę „zrozumie” jako datę. Czyli komputer przechowuje liczbę ale nam ludziom pokazuje daty jako „łańcuszki tekstowe” będące kombinacją liczb i separatorów. Rodzaj „łańcuszka znaków” uzależniony jest od wersji językowej zrozumiałej dla użytkownika. Prawda jakie proste?

Przykłady formatowania dat

Powyżej opisano, że Excel przechowując w komórce liczbę może nam ją przedstawić (wyświetlić formatem) jako zrozumiałą dla nas datę. Możemy do tego celu użyć kilkunastu propozycji standardowych lub sformatować komórkę niestandardowo. Możliwości pokazywania w sposób niestandardowy jest mnóstwo, najważniejsze z nich to wyświetlenie:

dni, poprzez użycie 1,2,3 lub 4 liter „d” od dzień lub day (wersja polska, angielska)

miesięcy, poprzez użycie 1,2,3 lub 4 liter „m” od miesiąc lub month (wersja polska, angielska)

lat, poprzez użycie 1,2,3 lub 4 liter „r” od rok (wersja polska) lub „y” od year (wersja angielska)

kombinacji tych liter z użyciem jakiegoś separatora

Poniżej na fotce przedstawiono przykłady formatowania niestandardowego dla polskiej wersji Excela 2007 [$-415]. W wersjach innych niż polska lub angielska używamy oczywiście odpowiednich liter dla wybranego języka, czyli „t” jak Tag dla niemieckiej wersji.

Ponownie zwracam uwagę, że Excel w każdym z tych przypadków przechowuje w

komórce tą samą liczbę, widać to chociażby po wyrównaniu wpisów wewnątrz komórek z datami do prawej krawędzi. Oczywiście w drugą stronę to nie działa. Czyli jeżeli dokonujesz wpisu do komórki bezpośrednio z klawiatury powinieneś go dokonywać według ściśle

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 5

określonego ustawienia danej wersji językowej. Tutaj są małe wyjątki, na przykład w polskim Excelu wpis 2012/01/07 zostanie zrozumiany i zaakceptowany jako data. A jak zostanie wyświetlony? Sprawdź sam . Natomiast wpis do komórki „poniedziałek” nigdy nie będzie liczbą.

Formatowanie czasu

W odróżnieniu od daty, na całe szczęście, praktycznie na całym świecie obowiązuje i jest zrozumiały ten sam format zapisu czasu określony jako gg:mm:ss. Na przykład zapis 01:15:38 to jedna godzina, piętnaście minut i trzydzieści osiem sekund. Często spotykany zapis skrócony, na przykład 01:15, to zawsze w Excelu będzie format gg:mm czyli jedna godzina i piętnaście minut a nie jedna minuta i piętnaście sekund (chyba, że dokonamy formatowania niestandardowego).

Przy wyświetlaniu wyników sumowania czasu nagminnie zdarza się, że wyświetlony czas (suma) jest pokazywany tylko w obrębie jednej doby, czyli niejako „zaokrąglony”. Na przykład w komórce A1 wpisz 15: i wciśnij Enter. W komórce A2 14: i zatwierdź enterem. Pod spodem w A3 oblicz sumę wyników i na olbrzymiej większości komputerów zobaczycie wynik 05:00 a nie 29:00. Oczywiście 5 godzin wynika z tego, że 29 – 24 (jedna doba) = 5. Pamiętajcie Państwo jednak o tym, że Excel w komórce przechowuje prawidłową wartość (liczbę odpowiadającą 29-ciu godzinom), manipuluje tylko jej wyglądem, wyświetlając czas z przedziału dla jednej doby.

Jak zatem wyświetlić pełny czas 29 godzin? Należy skorzystać ze standardowego formatu „37:30:55” lub sformatować niestandardowo, gdzie jednostkę godzin należy ująć w nawias kwadratowy, czyli [g]:mm:ss lub na przykład samo [g]. Tutaj znowu mamy sporo możliwości formatowania niestandardowego – popatrzmy na przykład poniżej, w A3 jest SUMA(A1:A2), a poniżej odwołania do A3.

Oczywiście znowu identyczna jak w przypadku dat uwaga. W każdym z przypadków

(od A3:A9) w komórce jest przechowywana ta sama liczba 1,208(3) natomiast sposób jej prezentacji ulega zmianie w zależności od ustawionego formatu. Zwracam uwagę, że w komórce A8 przykładu nie ma liczby 104400! Do ewentualnych obliczeń zostałaby wzięta liczba 1,208(3). Jak sobie z tym radzić? O tym poniżej.

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 6

Uwagi o wykonywaniu obliczeń na jednostkach czasu

Mając na uwadze różne sposoby prezentacji wyników przed rozpoczęciem obliczeń należy sobie odpowiedzieć na pytanie, czy obliczenia na jednostkach czasu będę wykonywał w systemie dziesiętnym, czy też na liczbach rzeczywiście przechowywanych (na ogół niewymiernych) a tylko wyświetlanych formatem w jednostkach czasu. Może najlepiej na przykładach:

Przykład 1 – oblicz wynagrodzenie

Załóżmy, że zatrudniliśmy malarza i przepracował on u nas 100 godzin. Uzgodniliśmy

stawkę wynagrodzenia na 10 PLN/godzinę pracy. Na fotce poniżej przedstawiono dwa sposoby rozwiązania zadania.

Stosując zapis nazwijmy go „godzinowym” musimy koniecznie wynik ostateczny

przemnożyć przez 24, ponieważ „sto godzin” w Excelu to nie jest liczba 100, tylko 4,1(6)… Liczba 100 to przecież 100 dni, czyli godzinowo 100x24=2400 godzin. Zapis w systemie dziesiętnym upraszcza obliczenia, ale wymagałby legendy w kolumnie B, żeby wiadomo było jak poprawnie wpisać ilość godzin.

Przykład 2 – czas pracy maszyny

Mamy zarejestrowane dane czasu pracy maszyny (3 cykle ciągłe), czas trwania dwóch przerw pomiędzy cyklami oraz ilość sztuk produktu wyprodukowanych w trakcje każdego z cyklów. Należy obliczyć czas zakończenia pracy maszyny, jej czas pracy netto i brutto. Czas pracy netto wyraź dodatkowo tylko w samych minutach. Ponadto należy obliczyć wydajność pracy maszyny (netto) na godzinę i minutę. Na zdjęciu poniżej przedstawiono rozwiązanie zadania.

Zapewniam Państwa, że próba wykonania przedstawionych poniżej obliczeń w systemie dziesiętnym, w szybkim tempie doprowadziłaby większość użytkowników Excela do małego obłędu . Zdecydowanie dla takich danych obliczenia należy przeprowadzać zgodnie z tym jak Excel „rozumie” czas, czyli przykładowo 7 minut zapisywać jako 00:07. Do uzyskania prawidłowych wyników wystarczą nam wtedy podstawowe operacje sumowania lub dzielenia, czasami wyniki obliczeń musimy korygować o 24 (dla godzin lub dodatkowo dzieląc przez 60 dla uzyskania minut) i ewentualnie dodatkowo je zaokrąglać.

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 7

Co zrobić, jeżeli moja data w Excelu nie jest datą?

Jeżeli Twoje dane w Excelu wyglądają jak daty (Ty je poprawnie odczytujesz) ale Excel nie interpretuje ich jako daty:

nie możesz tych danych prawidłowo sortować, nie możesz tych danych grupować w okresy charakterystyczne dla dat

(miesiące, kwartały, lata), oczywiście nie możesz wykonywać na tych danych działań matematycznych

Jest kilka sposobów aby dokonać konwersji tekstów wyglądających jak daty na

rzeczywiste daty, czyli liczby. Zaproponuję jeden, z użyciem narzędzia Tekst Jako Kolumny (TJK). Wadą tego rozwiązania jest niemożliwość jednorazowej konwersji większej liczby (więcej niż jedna) kolumn z datami. Zaletą jest uniwersalność, prostota i szybkość.

Zaznaczamy dane z datami (jedna kolumna), uruchamiamy narzędzie (menu Dane – Tekst Jako Kolumny), dwa pierwsze okna pojawiającego się kreatora pomijamy wciskając „Dalej”. W trzecim oknie zaznaczamy opcję DATA i z listy obok wybieramy układ daty, ale uwaga, taki jaki mamy w danych, a nie taki jaki ma być. Naciskamy „Zakończ” i gotowe.

Ciekawostka dla dociekliwych We wszystkich aktualnie obowiązujących wersjach Excela występuje pewien błąd w datach, który dla bardziej dociekliwych może stanowić zagadkę. Dotyczy on pierwszych kilkudziesięciu dat, ale … nie uprzedzajmy, oddajmy głos artik-owi Poniżej cytat wypowiedzi z excelforum.pl

B o g d a n G i l a r s k i w w w . e x c e l p e r f e c t . p l

Strona 8

Czy rok 1900 był rokiem przestępnym? NIE BYŁ. Od 1582 roku, zgodnie z tym co sobie papież Grzegorz XIII wymyślił i zadekretował

(od jego imienia mamy kalendarz gregoriański), w Polsce obowiązuje poniższy algorytm obliczania roku przestępnego: Kod:

Function czyPrzestępny(rok As Integer) As Boolean If ((rok Mod 4 = 0) And (rok Mod 100 <> 0)) Or _

(rok Mod 400 = 0) Then czyPrzestępny = True Else czyPrzestępny = False End If End Function Dlaczego więc Excel akceptuje datę 29 lutego 1900? Jest to świadomy błąd programistów Excela. Dlaczego świadomie dopuszczono ten błąd? Ano dlatego, że niejaka firma Lotus (to

od niej mamy takie nazwy produktów jak, już zapomniany, Lotus Organizer czy, bardziej znany, Lotus Notes) wypuściła dawno, dawno temu (lata 80.) na rynek bubel (pod nazwą 1-2-3), który zakładał, że rok 1900 był przestępny. Ponieważ Lotus w owych czasach był firmą wiodącą w arkuszach kalkulacyjnych, to M$ musiał do niego równać, a nie odwrotnie. Po prostu, M$ stwierdził, że naprawa błędu spowoduje niekompatybilność z arkuszami Lotusa i wolał go pozostawić, jak narazić się użytkownikom. Poniekąd rozumiem stanowisko M$, w tamtych czasach walczył o rynek. Ale wygląda na to, że do ... śmierci arkusze będą obciążone tą skazą genetyczną.

Niby z pogardą odniosłem się do narzędzia Lotusa lecz w pierwszej połowie lat 90 (wersja windowsowa) był to naprawdę wspaniały arkusz. Posiadał funkcjonalność, której Excel (moim zdaniem) dorównał dopiero w wersji 95 (była taka, była Val(Application.Version) = 7).

Powracając do sprawy, bo się trochę rozmarzyłem, dzień 1 stycznia 1900 to był PONIEDZIAŁEK (a nie niedziela, jak rzecze "nieomylny" Excel). Excel błędnie wskazuje dni tygodnia w zakresie dat: 01.01 - 28.02.1900 (przesunięcie o jeden dzień) oraz błędnie akceptuje datę 29.02.1900, której nie było. Tyle (pre)historii na dziś. Koniec cytatu – prawda, że fajne ? I jakie pouczające.

Podsumowanie Mam nadzieję, że po przeczytaniu artykułu i wykonaniu własnych testów w Excelu, problem daty i czasu stanie się dla Państwa błahostką i bez większych kłopotów będą Państwo korzystali z obliczeń na datach i jednostkach czasu. Nie omówiłem co prawda funkcji przeznaczonych do operacji na datach i jednostkach czasu, ale to może kiedyś? Bogdan Gilarski, styczeń 2013 Podziękowania dla master_mix-a za linka do stron z kodami języków oraz dla artik-a za ciekawy wykład o błędach programistycznych ciągnących się przez dziesięciolecia.