Czyli operacje na czasie

Zazwyczaj kiedy wykonujemy operacje na czasie, np. chcemy policzyć czas trwania jakiejś czynności – wystarczy nam proste odejmowanie godzin. Wynik jaki otrzymamy jest liczbą dziesiętną, czyli inną godziną, tradycyjnie wyświetlaną w formacie czasu, czyli 02:45. Natomiast nie zawsze chcemy tak ją wyświetlać.

Załóżmy, że tworzymy grafik czasu pracy. w którym chcemy zaznaczyć wszystkie godziny nadliczbowe (ponad 8h), ale w postaci takiej, że minuty będą wyświetlane jako część godziny. Czyli np. 02:45 → 2,75.

Excel sam z siebie godziny wyświetla jako części doby, a my potrzebujemy tutaj wyświetlić minuty, jako części godziny. Dość nietypowo, ale spokojnie do zrobienia :). Oto nasza formatka z wynikiem, jaki chcemy uzyskać:

Logika formuły

Cały problem w tej sytuacji polega na tym, że Excel traktuje czas jako część doby. Czyli godzina 12:00 będzie dla niego liczbą 0,5, 6:00 → 0,25 itd. Natomiast my chcemy, aby godziny były liczbami naturalnymi, a minuty były częścią godziny. Czyli np: 1:45 → 1,75, ponieważ 45 minut to 0,75 godziny.

A z dodatkowych utrudnień: ponieważ w formatce nie ma miejsca na obliczenie godzin nadliczbowych (rozumianych tutaj jako różnica między łącznym czasem pracy a 8 godzinami), to to przeliczenie będziemy dokonywać „w locie”.

Formuła więc będzie sprawdzała, czy w ogóle danego dnia pracownik pracował (czy jego łączny czas pracy jest większy od zera), a następnie będzie liczył owe godziny nadliczbowe, od razu zamieniając je na pożądany przez nas sposób wyświetlenia. Po drodze dokonamy jeszcze zaokrąglenia wyniku do 2 miejsc po przecinku (opcjonalnie oczywiście).

Formuła

Zacznijmy więc od środka, czyli od wyliczenia godzin nadliczbowych, w excelowy sposób, czyli jako część doby. Potem będziemy to przerabiać na to, co chcemy:

=F5-8/24

Odejmuję 8/24, ponieważ jest to liczba odpowiadająca 8:00.

Ok. Teraz z tego trzeba wyłuskać informację o godzinie, czyli wrzucimy sobie to do funkcji GODZINA:

=GODZINA(F5-8/24)

Następnie potrzebujemy minut, ale właśnie w formie części godziny, czyli:

+MINUTA(F5-8/24)/60

Teraz połączę to ze sobą i od razu zaokrąglę do 2 miejsc po przecinku:

=ZAOKR(GODZINA(F5-8/24)+MINUTA(F5-8/24)/60;2)

No i na koniec przyda się jeszcze warunek, żeby to wyliczenie się dokonywało tylko w przypadku, gdy pracownik danego dnia pracował dłużej niż 8 godzin (dla tej sytuacji liczę nadgodziny):

=JEŻELI(F5>8/24;ZAOKR(GODZINA(F5-8/24)+MINUTA(F5-8/24)/60;2);0)

I tyle! Wynik otrzymujemy taki:

Wynik

Proste i przyjemne :).

Powiązane produkty:

• WEBINAR: Funkcje dat – excelowy niezbędnik, omawiający najważniejsze funkcje dat, które pozwolą Ci w prosty sposób dokonywać obliczeń na datach, bez kombinowania jak koń pod górę.
• WEBINAR: Daty w Excelu, podczas którego omawiam czy są daty dla Excela i jak sobie z nimi radzić.

 A tutaj plik z gotowym rozwiązaniem do pobrania:

MalinowyExcel Minuty przedstawiane jako część godziny dw.xlsx

I film wideo na YouToube’ie: