Ewidencja czasu pracy w Excelu - jak obliczyć?

Witam.
Potrzebuje obliczyć ewidencje czasu pracy w Excelu.
W jednej komórce (suma godzin pracy) chciałbym zastosować funkcję uwzględniającą następujące kryteria :
1. Jeżeli godzina zaczęcia pracy ma wartość mniejszą, niż godzina zakończenia, to: godzina zakończenia - godzina zaczęcia = suma godzin pracy.
2. Jeżeli godzina zaczęcia pracy ma wartość większą, niż godzina zakończenia pracy, to: godzina zakończenia pracy (ma być domyślnie traktowana przez Excel jako godzina z kolejnej doby) - godzina zaczęcia pracy = suma godzin pracy.

Próbowałem już różnych sposobów - także tych znalezionych na Elektrodzie, ale w tych które zastosowałem Excel wyświetla mi niespójność formuły.

Będę zatem wdzięczny za pomoc.

Pozdrawiam,

DAREK

Witam
Ja bym tu zastosował funkcję LICZ.JEŻELI(zakres;kryteria)
http://office.microsoft.com/pl-pl/excel-help/licz-jezeli-HP005209029.aspx
Pozdrawiam

Formuła komórki C2:
=JEŻELI(A2<B2;B2-A2;JEŻELI(A2>B2;24-A2+B2;JEŻELI(A2="";0;JEŻELI(B2=""0))))

Komórki A2 do C32 muszą być sformatowane na niestandardowe > gg:mm , wszystkie inne sformatowane na ogólne.

Godz. początku i końca wpisujemy więc gg:mm

Problem jest z sumowaniem kolumny C, ponieważ suma jest MODULO 24h.
W związku z tym stosuję kolumny pomocnicze E - H i rozbijam czas pracy z kolumny C na godz. i min. (kol. E,F), dalej z sumy minut uzyskuję resztę godzin i minuty (komórki G34,H34) i tak dopiero spreparowane godziny sumuję w C34=E34+G34, a minuty w D34 = kopia H34.

Z pewnością można to zrobić bardziej elegancko, ale mnie się nie udało.
Być może korzystając z makr, ale ja makr nie znam.

Poniżej arkusz z wprowadzonymi przykładowymi danymi.
Powodzenia.

Nie trzeba...
Czas (godziny) w Excelu działa wg zasady: jedna doba ma wartość 1, a poszczególne godziny to część tej jedynki. Dlatego godz. 12:00 to w Excelu 0.5, a 6:00 rano to 0.25.

Formatowanie komórek to jedynie sposób prezentacji danych dla użytkownika. Jednak "w środku" komórka nadal ma taką samą wartość. Świetnie widać to np. przy formatowaniu komórki jako "liczba z 2 miejscami po przecinku" - na ekranie widać, że w komórce jest liczba typu 0.72, ale tak naprawdę cały czas jest tam 0.72345978432 (dane przykładowe). Formatowanie komórki nie zmienia jej wartości.

Różnica godzin więc to proste działanie matematyczne (+/-). Nie trzeba korzystać z dodatkowych funkcji w tym przypadku ani używać dodatkowych komórek.

To napisz może prościutki przykład jak byś to rozwiązał.
Na razie zmodyfikowałem trochę formuły - w/g Twoich sugestii - ale i tak nie obyło się bez kol. pomocniczej D , ponieważ trzeba oddzielnie zmodyfikować minuty bo
np. 36min. = 36/60 = 6/10 <> 36/100.
Poniżej formuła po zmianach.
Pozdrowienia.

Konkurs? A jaka nagroda? U mnie wyszło jak w załączniku.

adiabel napisał:

To napisz może prościutki przykład jak byś to rozwiązał

Wystarczy dodać "1", gdy różnica godzin jest ujemna.
W ułamku jakim jest godzina (wartość mniejsza od 1) zawarte są i godziny, i minuty. Nie warto ich rozbijać na oddzielne wartości w różnych komórkach, bo później znowu trzeba je łączyć, a nie ma w ogóle takiej potrzeby...

Dla całej tej zabawy z godzinami w Excelu trzeba tylko pamiętać, że godzina to zwykła liczba i tylko dzięki formatowaniu wygląda po "ludzku", np. 11:50. Można te "godziny" sumować i odejmować od siebie - to tylko liczby.

Do adamas_nt: w porządku tylko to chyba będzie służyć do obliczania wypłaty a więc raczej powinno być w formacie gg , min lub gg , min/0,6 (dziesiąte części godz.).

Do wszystkich: zmieniłem trochę wcześniejszy załącznik godz_prac1.xls.
Pozdrowienia.

adiabel Jeśli chcesz liczyć stawkę*ilość godzin to koniecznie trzeba sprowadzić czas do wartości dziesiętnej. Jeśli przepracowałeś półtorej godziny za 20,00zł/godz to: 1,30*20, czy 1,50*20?

Zauważ, że jest różnica w liczeniu czasu i ilości godzin.
OK, do mojej wiadomości przyda się informacja, że łącznie przepracowałem 3 dni, 3 godziny i 36 minut. Ale zapłacone chcę mieć za 75,6 godzin razy stawka i w tym momencie guzik mnie obchodzi ile to sekund, procent okrążenia księżyca wokół ziemi, przesypanego piachu w klepsydrze, etc. Myślę, że się zgadzasz

Jeszcze jedna zmiana Panowie:
dzięki sugestiom kolegi aldrin bardzo znacznie uprościłem formułę w kolumnie C.
To już chyba będzie ostateczna wersja.
Pozdro.

Do adamas_nt:
Jasne, że się zgadzam i tak jest teraz zrobione.
Nagrodą jest podziękowanie za zainteresowanie tematem.

Oj, nie jest dobrze.

Czy kolumna D jest potrzebna czy nie, to już sprawa dyskusyjna - w zależności od potrzeb. Niemniej jednak do obliczenia sumy godzin z zakresu ta kolumna w ogóle nie jest potrzebna.

Funkcja Wartość jest niepotrzebna - "godziny" w Excelu są liczbami !!

Formuła w kolumnie C jest błędna - nie można ZAWSZE dodawać 1, bo to oznacza dodanie 24 godzin pracy. Przykładowo: w komórce C2 mamy tak naprawdę 26:10 (gg:mm).



Naprawdę wystarczy zwykła funkcja Jeżeli w takiej koncepcji jak jest w kolumnie D. Nie trzeba tylko wstawiać funkcji Wartość, a komórki sformatować jak czas.

Zgadzam się; można tam dodać warunek: JEŻELI(B2<A2;B2-A2+1;B2-A2).
Nie zrobiłem tego bo u mnie wyświetla się normalnie C2=2:10 , a nie 26:10 , a przy zerowych wartościach w A,B lub ich braku w C jest 00:00.
Pracuję w Excel 97 może stąd różnica, nie wiem.

Kolumna C sformatowana na niestandardowe gg,mm i służy wyłącznie wizualizacji bo obliczenia są i tak prowadzone na kol. D.

Tak czy inaczej poniżej wersja poprawiona o warunek oraz zrzut ekranu z przed poprawki.

A swoją drogą to ciekawe, że nie odzywa się ten kolega, który temat założył.?

Witam.
Nie spodziewałem się tak wielkiego odzewu, więc chciałbym podziękować wszystkim osobom, które odpisały.
Jako, że nigdy nie pracowałem pod rząd dłużej, niż 24 godziny, to zwycięski okazał się warunek: =JEŻELI(C>D;D-C+1;D-C)
Jako dowód załączam plik, w którym opracowuje zestawienia godzinowe.
Mam jeszcze jedno pytanie...
Mianowicie czy można nauczyć Excela, żeby wyliczał mi godziny etatowe (kolumna E) w oparciu o poniższe warunki uwzględniające specyfikę mojej pracy:
- godziny etatowe trwały max. 8-10 godzin, ale czasami mniej, jak się spóźniłem i odpracowywałem je innego dnia;
- czasami przychodziłem na godziny etatowe i zostawałem na nadgodziny;
- czasami przychodziłem na same nadgodziny.

Na mój chłopski rozum żadna funkcja nie odgadnie, czy przyszedłem do pracy na nadgodziny, czy na sam etat tudzież na jedno i drugie w sytuacji, jeżeli dysponuje tylko godziną rozpoczęcia i zakończenia pracy, ale może ktoś z Was ma pomysł, jak to usprawnić???
Obecnie w kolumnę E (godziny etatowe) wpisuje z palca ilość godzin etatowych i na tej podstawie w kolumnie F (Nadgodz.) mam wyliczoną liczbę nadgodzin w oparciu o różnicę: G-E. Ale jest to łopatologiczne rozwiązanie, ponieważ jeżeli podmienię wartości w kolumnach C i D (rozpoczęcie i zakończenie pracy), to z palca muszę zmieniać wartość w kolumnie E.
Z jednej strony to jest logiczne, bo Excel nadal nie wie, ile godzin byłem na etacie tudzież czy w ogóle na nim byłem. Ale z drugiej strony rozwiązanie takie trochę mnie razi.
Czy wg Was da się to jakoś usprawnić, czy musi tak zostać?
Będę wdzięczny za pomysły i sugestię.

Odwróciłbym kolejność. Wpisywał "z palca" nadgodziny i liczył etatowe. Nadgodziny raczej nie zdarzają się codziennie i można zostawić komórkę pustą...

Dużo zależy od sposobu rozliczania nadgodzin w danym zakładzie pracy.

Jeżeli dany pracownik jest zatrudniony na A% etatu, to ma do wypracowania w danym miesiącu B godzin. Czasami, ze względu na warunki pracy, rozliczenie godzin nie jest miesięczne, ale kwartalne, choć z miesiąca na miesiąc nie można robić rażących przesunięć godzin pracy. Tak czy inaczej, w końcu w ramach etatu, pracownik ma do wypracowania pulę godzin i jeśli ten limit przekroczy, to ma nadgodziny. Trudno mówić o nadgodzinach gdy pracownik (z różnych powodów) w danym okresie rozliczeniowym wypracował tylko tyle godzin ile wynika z etatu choć pierwotnie planował wypracować więcej i nawet został kilka razy po godzinach, ale później skorzystał z tych nadwyżek i bilans wyszedł na zero.

Może spojrzeć na to od tej strony- bilans na koniec okresu rozliczeniowego?

Zmiana kolejności i wpisywanie z "palca" nadgodzin, zamiast godzin etatowych, to faktycznie bardzo dobry pomysł, bo istotnie godzin na etacie jest zdecydowanie więcej, niż nadgodzin, co sprawia, że można sobie zaoszczędzić czasu. Wykorzystam go w swoim zestawieniu - dzięki adamas_nt.
Aldrin - napisałeś o bilansie na koniec okresu rozliczeniowego. Jakbyś to dokładnie widział? Możesz zapodać załącznik?
Myślałem też o tym, żeby zapodać kolumnę z L4 i Urlopem Wypoczynkowym (w miesiącach, kiedy korzystałem z tych świadczeń), ale szerokościowo nie zmieściłyby się na A4, więc istotnie bilans jest lepszym rozwiązaniem.
A może ktoś ma jeszcze jakieś inne pomysły?

DAREK-2 napisał:

...ale szerokościowo nie zmieściłyby się na A4, więc istotnie bilans jest lepszym rozwiązaniem...

Ja tak na marginesie podpowiem że:
poza ewentualnym zmniejszeniem marginesów jest przecież jeszcze opcja "wpasuj w stronę".



I wtedy wszystko ci się zmieści.

Witam ponownie.
W oparciu o Wasze rady postanowiłem zmodyfikować nieco ewidencje dodając pola Urlop (kolumna G) oraz L4 (kolumna H). Zmieniłem także kolejność – teraz z palca trzeba wpisywać nadgodziny, zamiast godzin etatowych - tak jest łatwiej i szybciej.
Mam jeszcze jeden mały problem. Mianowicie, jeżeli będę miał urlop, to chciałbym, ażeby kolumny od C do F + kolumna H były przekreślone i nie pojawiały się w nich godziny. Teraz nawet jak przekreślę kolumnę E, to widać 00:00, bo jest na niej założona funkcja – patrz np. E3.
I analogicznie jeżeli będę na L4, to chciałbym, żeby kolumny od C do G były przekreślone bez widoczności godzin.
Powód: rozwiązanie wydaje mi się bardziej przejrzyste, niż widoczność zer godzinowych, które de facto psują efekt estetyczny.
No chyba, że ktoś ma lepszy pomysł?
Będę wdzięczny za sugestie.

Zmieniłem z lekka formuły i dodałem formatowanie warunkowe dla zakresu C3:D32. Przeanalizuj, dostosuj wg własnego uznania.

DAREK-2 napisał:

... dodając pola Urlop (kolumna G) oraz L4 (kolumna H)

Może to kwestia gustu czy upodobań, ale chyba wygodniej dla użytkownika gdy ma mniej kolumn w tabeli. Dlatego raczej redukowałbym ich ilość:
- kolumny A i B można umieścić w jednej formatując komórki
- urlop/L4 wpisywać w jednej kolumnie do tego przeznaczonej a może wręcz w uwagach?


W pierwszej komórce jest data, a w następnych +1. Nie trzeba ręcznie wpisywac dni tygodnia i mniejsza szansa na pomyłkę. W takim układzie kolumna B nie byłaby potrzebna, napis 'czerwiec' w każdej komórce znika i mamy więcej miejsca 'na inne rzeczy'.

Zakładając, że będzie 12 arkuszy (po jednym na miesiąc) przydałby się arkusz zbiorczy, gdzie będą zebrane dane z całego roku. Wygodniej też byłoby umieścić zestawienie miesiąca na górze strony, a poniżej dane szczegółowe (dzień po dniu):
- po zablokowaniu okienek zestawienie zawsze jest widoczne
- w arkuszu zbiorczym odwołania będą do tych samych komórek w poszczególnych arkuszach
- nie wiem jak jest w tym zakładzie pracy, ale jeśli jest tak, że ilość godzin do wypracowania zależy od ilości dni roboczych (po odliczeniu dni "świątecznych") - te dane też powinny być w tym arkuszu. Wtedy widać ile godzin było do wypracowania i ile jest faktycznych nadgodzin. Jeśli takie dane będą oczywiście istotne.
Ogólnie: niech użytkownik ma jak najmniej do wpisywania, to szansa na błąd będzie mniejsza, a i on będzie bardziej zadowolony.

DAREK-2 napisał:

.. chciałbym, żeby kolumny od C do G były przekreślone bez widoczności godzin.
Powód: rozwiązanie wydaje mi się bardziej przejrzyste, niż widoczność zer godzinowych, które de facto psują efekt estetyczny.

To przekreślenie chyba jest mało czytelne - może po prostu formatowanie warunkowe "zniknie" wartości zerowe i 'wyszarzy' komórkę? Ujednolicenie wyglądu do postaci gg:mm daje też czytelny wygląd, zwłaszcza w kolumnie E lub I.

adamas_nt - podałeś dobry patent - godziny w kolumnach E i J wyświetlają się wtedy, kiedy wartości nie są zerowe. O coś takiego mi chodziło.
Wizualnie wygląda to o wiele bardziej przejrzyście.
Tylko nie wiedzieć czemu w Twoim załączniku nie działa sumowanie godzin (kolumna I), jak wpiszę godziny z palca TYLKO w polach Urlop (kolumna G) i L4 (kolumna H). P.s. W pola Godz. rozpoczęcia i zakończenia pracy (kolumny C i D) nie wpisuje w takim przypadku nic, bo formalnie nie rozpoczynam i nie kończę pracy o danej godzinie, jak jestem na Urlopie tudzież L4.
Da się coś z tym zrobić?


Pozdro,

Ściągnąłem załącznik i u mnie jest OK.



Przy okazji: Wg sugestii kol Aldrin zautomatyzowałem wypełnianie kalendarza.

adamas_nt - niestety nadal nie działa sumowanie godzin w kolumnie I, po wpisaniu z palca godzin w kolumny H i J. Z tym, że nie chodzi mi o to podsumowanie łączne na samym dole, co załączyłeś na screenie, tylko sumowanie w oparciu o każdy wiersz przyporządkowany do kolumny I. Np. wpiszę z palca 8:00 w kolumnę H3, więc chciałbym, żeby mi się pojawiała suma w polu I3 i analogicznie z pozostałymi.
Pomysł ze zmianą miesięcy i lat z rozwijanej listy na samej górze uważam za bardzo dobry. Nawet nie sądziłem, że da się coś takiego zrobić w Excelu. Wielkie brava i słowa uznania. Myślałem, że jedynym rozwiązaniem jest wstawianie nowych arkuszy w ten sam plik z nazwą miesiąca i roku, żeby je potem łatwo odróżnić i szybko znaleźć potrzebne zestawienia.
Jednakże zauważyłem, że po zmianie miesiąca i roku zmieniają się tylko wartości w kolumnach A i B, a w pozostałych kolumnach godziny pozostają takie same, co sprawia, że nie można zapisać rozkładu godzin pracy dla danego miesiąca w roku.
Będę również wdzięczny, za napisanie, jak można dodawać i usuwać lata/miesiące na tej liście rozwijanej.

DAREK-2 napisał:

Np. wpiszę z palca 8:00 w kolumnę H3, więc chciałbym, żeby mi się pojawiała suma w polu I3 i analogicznie z pozostałymi.

Przeanalizuj zapis "sumujący" w kolumnie I
=JEŻELI(C5="";"";JEŻELI(C5>D5;D5-C5+1;D5-C5)+G5+H5)

Jak widzisz pierwszy warunek to to że aby wyświetlił się wynik musi być coś wpisane w C5 więc ...

Druga rzecz to kalendarz zapewne dodany po to by nie trzeba było z ręki wpisywać dat. niestety jak dobrze zauważyłeś on zmienia tylko daty a nie dane.
Więc dla każdego miesiąca trzeba tworzyć oddzielny arkusz lub skoroszyt.

A wybór miesięcy i lat jest zrealizowany poprzez sprawdzanie poprawności danych w komórce (menu dane-sprawdzanie poprawności)

Zatem będę wdzięczny za zmodyfikowanie funkcji, ażeby umożliwiła sumowanie godzin wpisanych z palca w kolumny H i J.

Sumowanie godzin z palca już działa, tak więc wielkie dzięki za pomoc.
Jako, że robię zestawienia dla wszystkich swoich miejsc pracy, to niestety okazało się, że nie da się zastosować jednolitego wzorca.
Zrobiłem więc nowy dodając jeszcze dwie kolumny.
Kolumna F (dzień) - powinny się w niej znaleźć wszystkie godziny dzienne - 7:00 - 23:00
Kolumna G (godziny nocne) - analogicznie - chodzi mi o wszystkie godziny nocne - 23:00 -07:00
Będę więc wdzięczny za podanie jeszcze jednej funkcji, która sumowałaby godziny dzienne i nocne w w/w kolumnach wg powyższego warunku.
P.s. Zgadzam się z Wami, że im mniej kolumn, tym lepiej, ale w tym akurat przypadku godziny nocne były lepiej płatne, niż dzienne, więc muszę mieć to uwzględnione.

Ja już się pogubiłem w które miejsca trzeba wpisywać te godziny - serio.
Tak, można się odnaleźć patrząc na wcześniejsze pliki, ale to chyba za duże utrudnienie? Czy ktoś jeszcze będzie korzystać z tego arkusza? Jeśli nie, to sprawa jest prosta, bo "dla siebie" można zrobić wszystko, zwłaszcza gdy robi się to od początku. Poza tym własne koncepcje podobają nam się najbardziej
Jeśli jednak ktoś jeszcze będzie z tego korzystać, to arkusz wydaje się przerośnięty lub robi się mało przejrzysty.

I tak:
- powinno wystarczyć wprowadzenie godzin od-do, a arkusz sam wyznaczy czy to godziny nocne, czy dzienne. Godziny dzienne to etat lub nadgodziny, a nocne? Czy może być tak, że pracownik przyjdzie na 20:00, a wyjdzie o 7:00?
- z tego co zaczyna mi się wydawać (ale głowy nie dam, że dobrze myślę) powinno być tak, że mamy wypracowane godziny (Od-Do), co w sumie daje xx godzin (kolumna K), z czego część została wypracowana w ramach etatu, część to nadgodziny, a reszta to nocne (??)
- mam wrażenie, że te nadgodziny i nocne wprowadzają niepotrzebne zamieszanie: nocne powinny wyliczać sie same, a nadgodziny są wtedy gdy wypracujemy etatowe w ramach np. miesiąca pracy. Nie jest tak?

Rozumiem, że obecnie może być tak: pracownik przyszedł o 8 rano, wyszedł o 19-tej, z czego do 16-tej byl na etacie, a 3 godziny to nadgodziny, ale rownie dobrze w ramach etatu mógł wypracować 3 godziny, a 8 to nadgodziny? Jeśli tak, to kolumny E i H pracownik sam musi uzupełnić, bo Excel tego nie rozwiąże.

Nurtuje mnie jeszcze kolumna urlop i L4 - skoro pracownik jest NIEOBECNY, to nie może wypracować ŻADNYCH godzin. Nie powinniśmy więc brać tych komórek w ogóle pod uwagę w formułach z danego dnia, tak?

Może pogrupować te kolumny w logiczne sekcje etat/nadgodziny, dzien/noc - obecnie zaczynają sie przeplatać. Może jednak zależy to właśnie od sposobu wyliczania pieniędzy i jest ustawione w dobrej kolejności

Jak to jest z tymi wątpliwościami?

Ewidencja z godzinami dziennymi i nocnymi ma się następująco:
- godziny etatowe - ustalone odgórnie do przepracowania w ciągu miesiąca;
- nadgodziny - wszystkie inne godziny przepracowane ponad etatowe, np. przyjście za kogoś innego, dodatkowa zmiana, etc.;
- godziny dzienne - godziny przepracowane w czasie 07:00 - 23:00;
- godziny nocne - godziny przepracowane w czasie 23:00 - 07:00, przysługuje za nie dodatek, dlatego ich liczba musi być wiadoma;
- L4 i Urlop - nie mogą być brane pod uwagę jako godziny przepracowane, bo pracownika nie ma fizycznie w pracy. Dlatego wpisuje je z palca w utworzonych w tym celu kolumnach. Ich suma jest po to, żebym wiedział, ile ich było. Są one też ujęte w oddzielnym polu na dole zestawienia.

Owszem - może być tak, że przyszedłem o 8 rano i wyszedłem o 19-stej, z czego etat, to 8 godzin, reszta nadgodziny. 3 godziny etatu i reszta nadgodzin z tego co kojarzę mi się nie zdarzyła, aczkolwiek teoretycznie też tak może być, jak jest dopełnienie godzinowe.
Grafik opracowuje na własne potrzeby.
Reasumując - wszystko już jest ok., za wyjątkiem tego, iż chciałbym, ażeby Excel wyliczał mi z automatu, ile miałem godzin dziennych i nocnych w dodatkowych kolumnach, które stworzyłem. Czasami miałem bowiem tak, że np. pracowałem 17:00 - 01:00, z czego 6 godzin było dziennych, a reszta nocnych. A Excel mógłby to wyliczyć z automatu.
P.s. Fakt, że grafik może być nie czytelny dla kogoś innego, ale dla mnie (w oparciu o specyfikę pracy) taka prezentacja poszczególnych kolumn wydaje się najlepszym rozwiązaniem.

Panowie mam taki problem excel nie chce mi dodawać godzin pracy a dokładnie mówiąc to nie chce dodawać minut. Chodzi mi o to że jak np. pracuje jednego dnia 11godz30minut a następnego tylko 11godz to excel podlicza mi 22godz a nie 22godz30minut proszę o pomoc bo już próbowałem różnych sposobów i nic nie działa.

Dodano po 39 [minuty]:

tu jest załącznik z moją listą

DAREK-2 napisał:

Czasami miałem bowiem tak, że np. pracowałem 17:00 - 01:00, z czego 6 godzin było dziennych, a reszta nocnych. A Excel mógłby to wyliczyć z automatu.

Tak, powinien to wyliczać. Także to, gdy ktoś przyjdzie o 19:00 i będzie pracować do 9:00, bo np. zmiennik nie dotarł lub jest inna pilna sprawa, tak?
Może być tak jak w załączniku?

Dodane:
zmodyfikowałem plik - a nuż będzie potrzeba przyjść do pracy wcześniej, np. o 5 rano i wyjść przed południem? Formuła rozrasta się, a być może niepotrzebnie i dlatego mam pytanie: ile godzin max. pracownik może być w pracy? Ile razy może 'przekraczać' w pracy krytyczne godziny 7 i 23? Tak realnie, nie teoretycznie. Wtedy można formułę uprościć. Na razie zostawiam jak jest (wariant roboczy).