Niestandardowy format czasu

Tradycyjnie już przypominam, że czas w Excelu to liczba.  Wszystko inne to tylko ozdobniki sposobu wyświetlania. Jeżeli komórki wyświetlające czas mają być używane do dalszych obliczeń – muszą być prawidłowo zapisane.

Choć oczywiście patrząc na dane np. w grafiku – zapisanie pracy w godzinach 0,3333333… do 0,66666667.. jest jednak mało przyjazne. Zdecydowanie trzeba to sformatować. 

Oprócz dostępnych standardowych formatów czasu, można oczywiście sformatować komórkę niestandardowo, dopasowując wygląd do własnych potrzeb.
Kody niestandardowego formatowania czasu to:

g
wyświetla godzinę 
gg
wyświetla godzinę z zerami uzupełniającymi
m wyświetla minuty
mm
wyświetla minuty z zerami uzupełniającymi
s
wyświetla sekundy
ss
wyświetla sekundy z zerami uzupełniającymi
[] wyświetla liczbę godzin większą niż 24 oraz liczbę minut i sekund większą niż 60
AM lub PM
wyświetla liczbę godzin w formie zegara 12-godzinnego

W praktyce wygląda to tak:

Liczba Wyświetlanie Kod formatowania niestandardowego
0,333333 8:00 g:mm
0,333333 08:00 gg:mm
1,25 30:00 [gg]:mm
0,6 02:24 PM gg:mm A/P”M”
0,333333 08:00 AM gg:mm A/P”M”

Kurs Excel 2013 od podstaw

Niestandardowy format daty

Na wszelki wypadek przypominam, że data w Excelu to liczba – to ważne. Z doświadczenia wiem, że kreatywność użytkowników w tym zakresie bywa niczym nie ograniczona i pojawiają się problemy, gdyż formuły nie działają prawidłowo, trudno zagregować dane czy nawet je posortować. Warto więc przypilnować, aby w komórkach była data prawidłowo zapisana, a wszystkie ewentualne ozdobniki czy nietypowe zapisy pokazać za pomocą formatowania komórek (czyli zmiany sposobu wyświetlania).

Kody niestandardowego formatowania daty to:

m wyświetla miesiąc jako liczbę
mm wyświetla miesiąc jako liczbę dwucyfrową (czyli dla numeru miesiąca mniejszego od 10 z dodaniem zera na początku)
mmm wyświetla skrót nazwy miesiąca
mmmm wyświetla pełną nazwę miesiąca
mmmmm wyświetla pierwszą literę nazwy miesiąca
d
wyświetla dzień jako liczbę
dd
wyświetla dzień jako liczbę z zerem uzupełniającym
ddd
wyświetla skrót nazwy dnia tygodnia
dddd
wyświetla pełną nazwę dnia tygodnia
rr
wyświetla rok w postaci 2 cyfr
rrrr wyświetla rok w postaci 4 cyfr

W praktyce wygląda to na przykład tak (dla daty dzisiejszej czyli 2019-09-09.

kod Wynik
dddd, d mmm rrrr poniedziałek, 9 wrz 2019
mmm, ddd wrz, pon
dd”\”mm”\”rrrrr
09\09\2019

Warto pamiętać też, że wprawdzie data to liczba, ale liczb ujemnych nie można sformatować w takim f0rmacie daty. Poza tym – wprowadzona data nie może być wcześniejsza niż 1 stycznia 1900r.


 

Kurs Excel - zaawansowane funkcje i formuły

 

 

Uniwersalna lista świąt

W wielu różnych rozwiązaniach Excela np.grafiki,  harmonogramy itp. istotnym elementem są daty świąt.  Pewne dni są przewidywalne – Nowy Rok zawsze wypada 1 stycznia, Święto Niepodległości – 11 listopada, ale co z datami świąt ruchomych? Owszem, można zajrzeć do kalendarza  i sprawdzić datę Wielkanocy czy Bożego Ciała, ale w kolejnym roku trzeba to zrobić ponownie. Na szczęście można to zrobić raz, a dobrze – wykorzystując po prostu formuły Excela.

Na rok 2019 wygląda to tak:

Po zmianie w komórce A1 roku na 2020 wygląda to tak:

Jak zrobić taką listę? W komórce A3 wyznaczającej datę Nowego Roku jest formuła:
=DATA(A1;1;1)
Podobnie dla A4:
=DATA(A1;1;6)
W analogiczny sposób ustawiamy daty świąt mających swoje stałe daty w kalendarzu.
Święta  ruchome powiązane są natomiast z Wielkanocą. Przypada zawsze w niedzielę po pierwszej wiosennej pełni księżyca. W Excelu wyznaczamy ją formułą w komórce A5:
=ZAOKR.W.DÓŁ(DATA(A1;5;DZIEŃ(MINUTA(A1/38)/2+56));7)-34
Drugi dzień Wielkanocy (komórka A6) jest już prosty:
=A5+1
Zielone Świątki to formuła:
=A5+49
Natomiast Boże Ciało (komórka A10) to:
=A5+60

W ten sposób można ustawić listę świąt na dany rok. Wystarczy zmienić wartość w oznaczającej rok komórce A1 i nastąpi automatyczne przeliczenie.

 

 

Kurs Excel - zaawansowane techniki

 

Data to liczba

Na każdym z przeprowadzanych przeze mnie szkoleń Excela, niezależnie od poziomu, zawsze przypominam: data w Excelu to liczba. I ma to duże znaczenie, szczególnie przy analizie danych.
Z własnej praktyki wiem, że kreatywność użytkowników w różnych sposobach wpisywania daty do komórek Excela jest niczym nieograniczona, a potem generuje to różne problemy.

Zapamiętajmy więc – data to liczba. To, co widzimy na ekranie – ładna data, gdzie nawet miesiąc jest wpisana słownie to tylko kwestia odpowiednio sformatowanego wyświetlania na ekranie, nadal jednak jest to liczba. Jej wielkość jest wyznaczona liczbą dni jakie upłynęły od “początku świata”, a konkretnie – od 1 stycznia 1900 roku.
Można się o tym przekonać samemu – w kolumnie A wstawiamy liczby, w kolumnie B – odniesienia do komórek z tego samego wiersza kolumny A (np. B1=A1). Kolumnę B formatujemy jako datę – efekt jak na obrazku:

W kolumnie A jest wszędzie wpisana liczba 43563.
W kolumnie B – odwołania do do sąsiednich komórek czyli np.
B2=A2, B3=A3 itd.
Oznacza to, że w kolumnie B jest także ta sama liczba, tylko inaczej sformatowana czyli wyświetlana na ekranie czy na wydruku.

Jak więc wpisywać do komórek Excela wartości daty? Przecież trudno za każdym razem przekształcać wartość daty na liczbę – nie miałoby to sensu. Chcąc mieć pewność, że do komórki wpisujemy na pewno datę, robimy to w formacie:
rrrr-mm-dd
np.2019-04-08
Wówczas nie będzie już niespodzianek.

A przy okazji – skrót klawiaturowy Ctrl+– wstawia do aktywnej komórki wartość bieżącej daty.