Praca z czasem w LibreOffice Calc

LibreOffice posiada mechanizmy zapewniające wygodę pracy z komórkami zawierającymi czas. Niekiedy jednak nie działają one tak jak należy, co prowadzi do uzyskania dość nieoczekiwanych wyników. Artykuł pokazuje, na co należy zwracać uwagę i jak rozwiązać niektóre problemy.

Zacznijmy od prostego ćwiczenia. Z poniższych danych chcielibyśmy dowiedzieć się, ile czasu łącznie była wykonywana dana czynność.

Rozpoczęcie	Zakończenie	Czas trwania
19:14:00	21:45:00	02:31:00
00:41:00	02:00:00	01:19:00
02:31:00	08:36:00	06:05:00
19:20:00	00:00:00	04:40:00
05:07:00	19:00:00	13:53:00
13:42:00	22:00:00	08:18:00
09:57:00	21:48:00	11:51:00
00:41:00	12:43:00	12:02:00
13:57:00	17:34:00	03:37:00
05:58:00	19:19:00	13:21:00

Oczywiście najpierw musimy przenieść je do arkusza kalkulacyjnego. Zaznaczamy kolumny w oknie przeglądarki, kopiujemy i przechodzimy do okna LibreOffice Calc. Tam wciskamy Ctrl + Shift + V. W pierwszym okienku wybieramy Niesformatowany tekst, a w następnym — Tabulator oraz Identyfikuj liczby specjalne.

Następnie w komórce C12 umieszczamy formułę sumującą:

=SUMA(C2:C11)

Dotychczas wszystko poszło jak z płatka. Jednak warto upewnić się, że dane w trzeciej kolumnie są poprawne. W tym celu przeliczymy je.

W komórce C2 umieszczamy poniższą formułę. Następnie kopiujemy ją do pozostałych komórek w zakresie danych.

=B2-A2

Chociaż otrzymane wyniki nie uległy zmianie, z naszą sumą stało się coś dziwnego — została zmniejszona o 24 godziny!

Winę za to ponosi piąty wiersz danych, w którym datą zakończenia jest północ. Człowiek rozumie, że chodzi tutaj o godzinę 24 (północ dnia następnego), ale arkusz kalkulacyjny nie jest tak inteligentny. Uznaje więc, że chcemy odjąć liczbę dodatnią (godzinę dziewiętnastą) od zera (północy). Oczywiście wynikiem takiego działania jest liczba ujemna. W rezultacie nasza suma zostaje o nią pomniejszona.

Sprawdźmy, czy jest tak naprawdę. Po umieszczeniu kursora w komórce C5 wybieramy z menu FormatWyczyść formatowanie bezpośrednie (lub wciskamy skrót klawiaturowy Ctrl + M). Zgodnie z oczekiwaniami, zawartość zostanie zmieniona na ujemną.

Istnieją przynajmniej dwa sposoby na usunięcie tego problemu.

Pierwszym jest modyfikacja zawartości komórki B5. Jeżeli północ zapiszemy jako „24:00:00”, różnica zostanie obliczona zgodnie z naszą intuicją.

Okno arkusza kalkulacyjnego z przykładową bazą danych. Północ zapisana jako „24:00:00” gwarantuje uzyskanie odpowiedniego wyniku odejmowania.

Drugim obliczenie różnicy z wykorzystaniem formuły warunkowej.

Wpierw przy pomocy funkcji JEŻELI sprawdzimy, czy liczba z kolumny B jest większa od liczby z kolumny A. Jeżeli tak, po prostu obliczymy ich różnicę. Jeżeli nie, liczbę w komórce B powiększymy o 24 godziny; tym samym damy arkuszowi do zrozumienia, że chodzi o tę godzinę następnego dnia.

Należy przy tym pamiętać, że czas jest wewnętrznie przechowywany jako zwykła liczba w formacie dziesiętnym. Część całkowita oznacza dni, część ułamkowa zaś — godziny, minuty i sekundy.

Odpowiednia formuła wygląda następująco:

=JEŻELI(A2<B2;B2-A2;B2+1-A2)

Okno arkusza kalkulacyjnego z przykładową bazą danych. Formuła warunkowa gwarantuje uzyskanie odpowiedniego wyniku odejmowania.

Pozostaje jeszcze jedno pytanie: dlaczego w komórce C5 arkusz wyświetlił wynik niezgodny z prawdą?

Winny jest temu format komórki. Otóż LibreOffice rozróżnia dwa kody formatu godziny: HH oraz [HH]. Ten pierwszy oznacza godzinę w formacie „zegarowym”, czyli z zakresu 0‒23; drugi oznacza liczbę godzin bez jakichkolwiek ograniczeń.

Podczas automatycznego rozpoznawania liczb specjalnych, Calc formatuje godziny zgodnie z kodem HH. Gdy, w wyniku odejmowania, zawartość komórki wykroczyła poza dopuszczalny zakres, arkusz zmodyfikował ją w taki sposób, aby znów się w nim znalazła. Jednak liczba rzeczywiście znajdująca się w komórce pozostała bez zmian i wpłynęła na wynik sumowania.

Ograniczenia formatu HH zobaczymy również wtedy, gdy usuniemy z naszej bazy sekundy.

W tym celu zaznaczamy wszystkie komórki z liczbami, klikamy prawym przyciskiem myszy i wybieramy z menu Formatuj komórki.... W nowym oknie na liście po lewej stronie zaznaczamy Czas, zaś na liście po prawej — 13:37 (kod formatu: HH:MM).

Po zatwierdzeniu zmian zwróćmy szczególną uwagę na komórkę sumowania. Pokazuje ona znacznie zaniżoną liczbę. Ponownie winny jest wybrany przez nas format. Powoduje on, iż po osiągnięciu pełnych 24 godzin licznik się „przekręca” i rozpoczyna od zera. Wyświetlana liczba godzin to reszta pozostała po usunięciu pełnych dni.

Przy okazji warto dodać, że takie same zmodyfikowane kody formatów są dostępne dla minut i sekund. Gdy chcemy wyświetlić w komórce 63 minuty, powinniśmy skorzystać z kodu [MM].

Podsumowując. Podczas pracy z czasem należy pamiętać przede wszystkim o tym, że zawartość komórki i sposób jej wyświetlenia to dwie odrębne kwestie. Jeżeli zauważymy jakiś nieoczekiwany wynik, w pierwszej kolejności przywróćmy formatowanie domyślne. Wiele błędów jest wynikiem wyłącznie zastosowania niewłaściwego kodu formatu.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

komentarze 4

  • Jarek napisał(a):

    Witam

    Sumuję godziny pracy w arkuszu – suma w formacie [HH]:MM i wynikiem jest np: 46:15 – suma zgodna, ale jak to zamienić na ułamek żeby wymnożyć przez stawkę? 46:15 powinno dać 46.25 – 46 godzin i 0.25 godziny (15 minut)

    • Mirosław Zalewski napisał(a):

      Daty są przechowywane wewnętrznie jako liczba dni od daty granicznej. Czas (godziny i minuty) to części dnia, wyrażone są więc w części ułamkowej.

      Nie ma potrzeby niczego zamieniać na ułamek. Wystarczy jedynie uspójnić jednostki, czyli obliczyć stawkę dzienną (w rozumieniu dosłownym a nie prawnym). Ostatecznie zadanie sprowadza się do formuły: CZAS_PRACY * STAWKA * 24

  • grzegorz napisał(a):

    Po zatwierdzeniu zmian zwróćmy szczególną uwagę na komórkę sumowania. Pokazuje ona znacznie zaniżoną liczbę. Ponownie winny jest wybrany przez nas format. Powoduje on, iż po osiągnięciu pełnych 24 godzin licznik się „przekręca” i rozpoczyna od zera. Wyświetlana liczba godzin to reszta pozostała po usunięciu pełnych dni.
    Tak właśnie mi się sumuje, ale jak to poprawić ?
    Grzegorz

  • jasc napisał(a):

    Udało się – ale dopiero jak po zmianie w oknie dialogowym formatowania z HH:MM na [HH]:MM kliknąłem DODAJ do funkcji użytkownika – inaczej jako wartość sumy w kolumnie godzin dostawałem 3,70 zł 🙂