Ile jest aktywnych polis ubezpieczeniowych?

Jakiś czas temu jedna z czytelniczek bloga zapytała mnie, w jaki sposób obliczyć ile polis ubezpieczeniowych z jej listy jest aktywnych. O każdej polisie wiemy kiedy się zaczęła i jaka jest jej data ważności. Interesuje nas: ile polis na dany dzień (dziś) jest aktywnych? Pokazaną metodę możemy zastosować w milionie innych sytuacji: czy pracownik pracował w interesującym cię okresie, data ważności produktu/faktury (choć tutaj wystarczy tylko data do - zobacz tutaj), realizacja projektu w terminie itd...

Korci mnie, żeby od razu wyliczyć ile czasu zostało do przeterminowania polisy i żeby, jeśli termin jest bliski, na tej podstawie wyświetlać jakiś komunikat lub kolorować zbliżające się daty... Ale to w kolejnych wpisach :)

Oto formatka:

Czytaj dalej-->

Data najbliższego przelewu

Dzisiaj będzie rozbudowanie tematu z poprzedniego wpisu, w którym opisywałam jak poznać datę np. pierwszego czwartku miesiąca. Wykorzystałam do tego dość skomplikowaną formułę (ale formułę! nie makro ;)), zawierającą aż 4 funkcje. Dziś sobie to rozszerzymy, ponieważ założenia są takie:

W naszej firmie przelewy wychodzą tylko w pierwszy albo trzeci czwartek miesiąca. Jeśli faktura się "nie załapie" na pierwszy czwartek - jest płacona w trzeci czwartek, jeśli na niego się "nie załapie: - dopiero w pierwszy czwartek następnego miesiąca. Itd...

Straszne :)

Czyli, z danych do zadania potrzebujemy na pewno termin płatności faktury i kilka komórek roboczych (można byłoby je wrzucić bezpośrednio do formuły - jak chcecie). Formatka do tego zadania wygląda tak:

Czytaj dalej -->

Pierwszy czwartek miesiąca

Dziś temat, który miałam z tyłu głowy już dość długo. Pierwszy raz zaczęłam się zastanawiać na nim, kiedy tworzyłam brytyjski grafik czasu pracy. Anglicy mają kilka świąt, które wypadają zawsze w pierwszy poniedziałek miesiąca. Akurat nie było konieczności, abym wprowadzała taki warunek do arkusza, jednak zastanowił mnie on. Ponieważ od razu nie wymyśliłam rozwiązania, zostawiłam temat. Niestety nadal siedział mi on w głowie. :)

Niedawno odezwała się do mnie Marta, która pracuje w księgowości, i odgrzebała kwestię spod gruzów innych pomysłów. Marta ma bardziej skomplikowany problem, jednak zagadnienie, które dzisiaj omówię, jest niezbędne do jego rozwiązania. Pokażę dziś, jak się ustala datę np. pierwszego czwartku miesiąca. Napisałam "np.", ponieważ zaprezentowaną metodą można ustalić dowolny numer dowolnego dnia tygodnia (oczywiście bez przesady - nie będziemy ustalać 15. poniedziałku miesiąca, bo taki przecież nie istnieje). ;) Aby formuła była uniwersalna, będziemy potrzebowali następujących danych:

1. Który z kolei dzień nas interesuje (np. pierwszy).
2. Jaki dzień tygodnia (np. czwartek).
3. W jakim miesiącu (np. październik).
4. W którym roku (np. 2016).

Formatka jest prościutka i wygląda tak:

Czytaj dalej -->

Obliczanie czasu pracy: godziny nocne REAKTYWACJA

Dziś chciałam napisać o czymś innym, ale Łukasz skutecznie skłonił mnie, abym znowu zajęła się tematem godzin nocnych;) Łukasz zauważył, że w poprzednim wpisie, a co za tym idzie - w poprzedniej formule - jest błąd. Nieprawidłowo liczyła ona bowiem czas pracy nocny, gdy pracownik zaczynał pracę o 6:00 (godzina graniczna godzin nocnych), a kończył o 23:00 (już w trakcie godzin nocnych). Czyli przepracował 1 godzinę nocną. Łukasz - dzięki wielkie za uwagę i wytrwałość w przypominaniu mi o temacie ;)

Poprzednia formuła wyglądała tak:

=JEŻELI(ORAZ(A6>=nocna_do;A6<nocna_od;B6>nocna_do;B6<=nocna_od;D6<=nocna_od);0;JEŻELI(ORAZ(C6>=nocna_od;D6<=nocna_do+1);D6-C6;JEŻELI(ORAZ(C6<nocna_od;D6<=nocna_do+1;C6>nocna_do);D6-nocna_od;JEŻELI(ORAZ(LUB(C6>=nocna_od;C6<nocna_do);D6>nocna_do+1);nocna_do+1-C6;JEŻELI(ORAZ(C6<nocna_od;D6>nocna_do+1);nocna_do+1-nocna_od;0)))))

Przyznam, że perspektywa analizy formuły-tasiemca wcale mi się nie widziała... Zabierałam się więc do tego jak pies do jeża. Nie mogłam jednak odkładać tego w nieskończoność, więc w końcu zasiadłam do pracy. Oczywiście, jak zobaczyłam formułę (dla przypomnienia wyklejam ją powyżej), to się przeraziłam! Analiza jej zajęłaby mi wieki i z pewnością poszarpałabym sobie na niej nerwy, a tego chciałam za wszelką cenę uniknąć ;) Stwierdziłam więc, że napiszę ją od nowa. Oczywiście oznaczało to dla mnie wgryzanie się w temat na nowo i wymyślanie wszystkich możliwych opcji na nowo... Ech, no cóż. Do dzieła!

Czytaj dalej -->

Przeterminowane faktury

Pokażę wam dziś jak sprawdzić, czy faktura jest przeterminowana. Temat szczególnie przydatny księgowym, ale nie tylko. Możemy bowiem sprawdzać termin wykonania zadania zleconego pracownikowi (to np. dla Project Managerów) czy termin ważności produktu spożywczego, czy jakiegoś kosmetyku. Zastosowań mnóstwo, a formuła i sposób sprawdzenia tego w Excelu - taki sam, niezależnie co sprawdzamy: faktury, deadline, szampon do włosów, jabłka czy gruszki :) We wpisie będę sprawdzać faktury. Zobaczcie, tak wygląda mój arkusz:

Przeterminowane faktury formatka[/caption] Czyli będę chciała określić status faktury (czy jest przeterminowana, czy nie) i dodatkowo określę o ile dni jest przeterminowana. Na koniec, żeby dodać trochę grafiki, którą tak wszyscy kochamy :), zaznaczymy kolorem te wiersze, w których występuje faktura przeterminowana.

Czytaj dalej -->

Jak ustalić ostatni dzień miesiąca?

Hehe, no właśnie. Niedawno się nad tym głowiłam, a to taka prosta sprawa (choć nie na pierwszy rzut oka). Potrzebowałam tego w wielu sytuacjach: liczenie średnich kosztów z określonych miesięcy, uniwersalny grafik czasu pracy, w którym chciałam, aby na podstawie podania miesiąca, wyświetlały się wszystkie dni danego miesiąca i takie tam. 

Pierwszy dzień miesiąca łatwo ustalić: zawsze dzień = 1. Z ostatnim dniem jest gorzej, ponieważ miesiące mają różną liczbę dni, a luty to już w ogóle (właśnie, ile dni ma w tym roku? Odpowiedź na końcu artykułu:))!

Dziś pokażę wam 2 sposoby na rozwiązanie tego problemu. Oczywiście jest ich więcej, jednak moje są dość łatwe. Zobaczycie :)

Będę chciała ustalić pierwsze dni następujących miesięcy (na formatce):

 

Czytaj dalej -->

Obliczanie ceny za usługę w zależności od czasu jej trwania

Ile klient powinien zapłacić za usługę (np. konsultację księgowej), która trwa 1 godzinę i 15 minut? A ile w przypadku, gdy za pierwszą godzinę płaci więcej niż za każde kolejne? A ile, jeśli ma płacić za każdą rozpoczętą godzinę?

Z takimi pytaniami zgłosił się do mnie jeden z czytelników bloga. Podobne pytania zadają sobie wszystkie te osoby, które mają ustalić cenę za usługę w zależności od czasu jej trwania. Oczywiście tego typu sprawy można dość szybko wyliczyć ręcznie, jednak na blogu piszę o usprawnieniach pracy, a ten przypadek aż się prosi, żeby go usprawnić :)

Ponieważ mogą być różne zasady rozliczania usług, w tym artykule rozpatrzę dwa ich przypadki.

wariant 1: klient płaci proporcjonalnie za każdą minutę kolejnej godziny,

wariant 2: klient płaci za każdą rozpoczętą godzinę pełną stawkę.

W obu przypadkach:

1. pierwsza godzina ma inną stawkę niż kolejne,
2. za pierwszą godzinę płaci się pełną stawkę.

Czytaj dalej -->

Obliczanie czasu pracy: jak odliczyć przerwy od przepracowanego czasu?

Jak zapewne zauważyliście, na moim blogu znajduje się sekcja o nazwie Pytanie... Sekcję tę stworzyłam po to, abyście mogli zadawać mi za jej pośrednictwem excelowe pytania (nie zawsze dany temat jest już już na blogu). I zadajecie ich dużo - super! Dzięki temu możemy pogłębiać swoją excelową wiedzę ;)

Ostatnio właśnie w tej sekcji napisał Woodberry. Chodziło o to w jaki sposób pracownikom, którzy pracują co najmniej 6 godzin w ciągu dnia odliczyć przerwę 30 min od łącznego czasu pracy danego dnia. Czyli: jeśli pracownik pracuje minimum 6 godzin – należy mu się bezpłatna przerwa. W takim wypadku chcemy poznać czas, jaki przepracował ten pracownik, bez wliczania przerwy do czasu pracy. A jeśli pracuje krócej niż 6 godzin – przerwa mu się nie należy, więc liczymy po prostu czas pracy, jaki przepracował.

W tym artykule pokażę sposób, w jaki to obliczyć. Woodberry - specjalnie dla Ciebie!

Czytaj dalej -->

Obliczanie czasu pracy: spóźnienia do pracy

Artykuły o czasie pracy zaczęłam pisać od najważniejszego, czyli Obliczanie czasu pracy (proste). Stwierdziłam, że jest to temat ważny i jednocześnie taki, którego wyliczenie często sprawia dużo problemów. Nie planowałam pisać o tym czegoś więcej. Okazało się jednak, że temat jest dla was megaprzydatny :) Zaczęliście pytać jak wyliczyć przerwy niewliczane do czasu pracy, jak oznaczyć czas pracy w nocy i inne (pełna lista artykułów na temat czasu pracy znajduje się poniżej). Ostatnio kilkoro z Was pytało, jak obliczać spóźnienia do pracy. W tym artykule dokładnie to opiszę.

Dowiesz się jak obliczyć:

1. czas spóźnienia,
2. czas przepracowany,
3. czas do odpracowania.

Artykuły z cyklu Obliczanie czasu pracy

1. Obliczanie czasu pracy (proste)
2. Obliczanie wynagrodzenia za przepracowany czas
3. Obliczanie stawki godzinowej
4. Obliczanie czasu pracy: tygodniowy czas pracy i nadgodziny
5. Obliczanie czasu pracy: równoważny czas pracy i wynagrodzenie za nadgodziny
6. Obliczanie czasu pracy: oznaczenie pracy w nocy
7. Obliczanie czasu pracy: przerwy niewliczane do czasu pracy
8. Obliczanie czasu pracy: godziny nocne
9. Obliczanie czasu pracy: jak liczyć spóźnienia do pracy

Czytaj dalej -->

Obliczanie czasu pracy: godziny nocne

Temat, który koniecznie trzeba poruszyć przy omawianiu czasu pracy: godziny nocne. Dziś będzie mnie interesowało:

• ile czasu pracownik przepracował (ogólnie),
• ile przepracował w trybie dziennym,
• a ile w nocnym.

Kasiu i Sebastianie - ten artykuł dedukuję Wam. Skutecznie zmotywowaliście mnie bowiem, bym przerzuciła go na początek kolejki i napisała go jak najszybciej :). Myślę, że ten artykuł odpowie również na podobne pytania innych osób, np. Łukasza o nadgodziny w godzinach nocnych. Koniecznie napiszcie o tym w komentarzach!

Oto i on: artykuł dłuuugi i bardzo wyczekiwany!

Czytaj dalej -->

Ile pracowników jest na urlopie?

Upał za oknami, środek okresu urlopowego. Dzisiejszy artykuł będzie więc "na czasie", ponieważ pokażę Wam, w jaki sposób sprawdzić ile pracowników pracuje w danym dniu i czy jest to wystarczająca ich liczba. Odpowiedź na to pytanie chciał poznać jeden z czytelników mojego bloga.

Z pewnością prezentowany tutaj przeze mnie sposób przyda się tym z was, którzy pracują w dziale HR lub przykładowo w dziale sprzedaży czy dziale obsługi klienta.

Załóżmy, że w dziale sprzedaży pracuje 15 osób. Chcę, aby codziennie pracowało minimum 60% zespołu, czyli 9 osób. Jeśli ten warunek nie jest spełniony - Excel ma mnie o tym poinformować (napisze mi, że są braki i obramuje komórkę na czerwono).

Efekt wygląda tak:

Teraz pokażę Wam jak to zrobić!

Obliczanie średniego czasu trwania (biegu na 10 km)

Ostatnio napisała do mnie Dorota - czytelniczka mojego bloga. Zadała ona pytanie:

Muszę w pracy wyliczyć średnią z kilku dni (ok. 100), dla próbek cito. (...) Do wyliczenia czasu trwania analizy pojedynczej próbki wzorowałam się na artykule Obliczanie czasu pracy, ale jak z tego wyliczyć teraz średnią dla wszystkich próbek?

W tym artykule pokażę zatem jak obliczyć średni czas trwania danej czynności. Omówię to na przykładzie biegu na 10 km. Będę potrzebowała do tego listę uczestników biegu (zawodników), a na niej godzinę startu i końca biegu.

Oto dane:

Obliczanie liczby wykorzystanych dni urlopu

Zbliża się czas urlopów - pomyślałam więc, że pokażę Wam jak w prosty sposób obliczyć liczbę wykorzystanych dni urlopu. Oczywiście wezmę pod uwagę tylko dni robocze (nie będę uwzględniać świąt).

Tak wygląda formatka:

 A jedyne czego potrzebuję to:

• data początku urlopu (w moim przykładzie to poniedziałek 2014-08-11),
• data końca urlopu (w moim przykładzie to piątek 2014-08-22) i
• lista dni wolnych od pracy, przypadająca na okres urlopu.

Przerwy niewliczane do czasu pracy

Dziś pokażę jak obliczyć czas pracy w przypadku, gdy nie mają być wliczane w niego przerwy. Jest to odpowiedź na pytanie jednego z czytelników mojego bloga, które brzmi tak:

Jak poprawić formułę pokazaną w poście Tygodniowy czas pracy i nadgodziny, jeżeli mam podaną przerwę w pracy np. od 12:00 - 13:00, a nie chcę jej wliczać w łączny czas pracy?

Tak wyglądają dane:

Sortowanie tekstu tak jak liczb

Ten post odpowiada na pytanie zadane przez KRISSP, które brzmi tak:

W jaki sposób zrobić sortowanie numerów w zapisie 1/1/2014, 1/2/2014...2/1/2014, 2/2/2014....150/1/2014 itd. Problem w tym, że sortowanie odbywa się alfabetycznie dla cyfr, czyli pokazuje mi wszystkie zapisy od cyfry 1 i wszystkie zapisy z cyfrą 1-czyli 1, 10,11,1....100.101,120, potem wszystkie zawierające cyfrę 2 itd. Więc np. 2/1/2014 wyrzuca mi gdzieś daleko, a powinien zaraz na początku. Chciałbym sortowanie od 1 do końca, ale wg ich kolejności.

Czyli mamy następujące teksty: 1/1/2014, 1/2/2014, 11/1/2014, 11/2/2014, 150/1/2014, 2/1/2014, 2/2/2014, 20/1/2014, 21/1/2014 (obrazek poniżej)

a chcemy, by były sortowane według liczby przed pierwszym ukośnikiem, czyli tak: 1/1/2014, 1/2/2014, 2/1/2014, 2/2/2014, 11/1/2014, 11/2/2014, 20/1/2014, 21/1/2014, 150/1/2014.

Dodatkowo, pokażę w jaki sposób posortować te teksty również po drugiej liczbie (między ukośnikami): 1/1/2014, 2/1/2014, 11/1/2014, 20/1/2014, 21/1/2014, 150/1/2014, 1/2/2014, 2/2/2014, 11/2/2014

Kto ma urodziny w ciągu 2 miesięcy?

Jedna z czytelniczek mojego bloga zadała następujące pytanie:

W arkuszu "Arkusz1: mam dane w których są pola "imię" "nazwisko" "data urodzenia". W jaki sposób do "Arkusz2" wrzucić tylko te dane osób, które mają urodziny w ciągu 2 miesięcy?

W tym poście pokażę w jaki sposób zdecydować, które osoby mają urodziny w ciągu 2 miesięcy. Posiadając taką informację - za pomocą na przykład autofiltru - można już bardzo łatwo skopiować dane tych osób do innego arkusza.

Tabela z danymi wygląda następująco:

Czas pracy - oznaczenie pracy w nocy

Post ten napisałam w odpowiedzi na pytanie jednego z czytelników mojego bloga. Jego pytanie brzmiało:

jak skonstruować formułę, aby zaznaczała które godziny są przepracowane jako "nocki", tj. od 22.00 - 6.00, czy można ustalić tak, aby pokazywało np. literę N (czyli nocka)?

Do rozwiązania tego problemu przyjęłam następujące założenia:

• pracownik pracuje w nocy (nocka) wtedy, kiedy przychodzi do pracy z datą o 1 dzień wcześniejszą niż wychodzi (np. przychodzi 13 stycznia a wychodzi 14 stycznia),
• godzina rozpoczęcia pracy jest większa niż godzina jej zakończenia (np. rozpoczęcie: 22:00, zakończenie 6:00).

Tabela danych wygląda następująco:

Równoważny czas pracy i wynagrodzenie za nadgodziny

W tym poście, w odpowiedzi na pytanie Kwolany (dotyczyło posta: Tygodniowy czas pracy i nadgodziny), pokażę jak obliczyć wynagrodzenie za nadgodziny przy elastycznym czasie pracy.

Przez elastyczny czas pracy rozumiem tutaj, że pracownik:

• przychodzi na ustaloną godzinę do pracy (np. 8:00),
• wychodzi o wybranej przez siebie porze,
• jednak zaległe godziny musi odpracować później.

Zakładam również, że pracuje na pełen etat, czyli 8 godzin dziennie.

Tabela danych wygląda następująco:

Tygodniowy czas pracy i nadgodziny

Nadszedł czas na obliczenie długiego czasu pracy, czyli na przykład z kilku dni czy tygodni. W takim przypadku suma godzin przekracza zwykle 24 i Excel zaczyna się dziwnie zachowywać... To znaczy Excel zachowuje się normalnie, jednak inaczej niż byśmy tego chcieli. Po zsumowaniu czasu pracy z całego tygodnia okazuje się, że przepracowany czas to przykładowo zaledwie 3 godziny... Tak jak na rysunku poniżej:

Dlaczego tak się dzieje?

Obliczanie stawki godzinowej

W poprzednich artykułach omawiałam jak obliczyć czas pracy (tutaj) oraz wynagrodzenie, jakie za ten czas przysługuje pracownikowi (tutaj). Teraz pokażę jak obliczyć stawkę godzinową, na podstawie wynagrodzenia (płaca) oraz przepracowanego czasu (czasu pracy).