EVIDENCIJA RADNOG VREMENA u EXCELU (evidencija o radnom vremenu radnika)

Prema Pravilniku o prijenosu podataka o radnom vremenu mobilnih radnika i o vođenju evidencije radnog vremena djelatnika u knjigovodstvu potrebno je voditi radno vrijeme radnika,  iskoristite Excel i kreirajte obrazac (šprancu, predlošku, template) za evidenciju radnog vremena radnika.

U koliko želite sami kreirati jednostavnu tablicu za knjigovodstvo (šprancu ili šihtericu) i voditi evidenciju radnih sati radnika (evidenciju radnog vremena radnika u Excelu) pogledajte ovaj tutorijal vodič koji vam može pomoći u kreiranju i postavljanju formula i funkcija da bi kompletirali obrazac.

PRIMJER 1.

ŠIHTERICA za proračun radnih sati radnika (Radnici i radno vrijeme)

U ovom primjeru prikazat ću vođenje radnih sati za svakog radnika (djelatnika) posebno a na kraju automatski svi podaci se kopiraju u zbirnu tablicu (sve ostalo je vaša kreativnost i mašta).

Kao prvo, kreirao sam popisne liste tj. definirao imena za neke osnovne podatke koji se pojavljuju u tablici a to mogu biti )GO-godišnji, BO-bolovanje, SD-slobodan dan, P-praznik). Ove liste su nam važne da možemo upisom dotične liste dobiti rezultat radnih sati u formatu vremena. ( nije dobro u formuli postavljati za pojedinu listu BROJ kao sat, jer format formule tada neće dobro dati rezultat.

Dakle na jednom listu sam definiraop popisne liste. Taj list sam nazvao imenom "NEDIRAJ" a u stvari to nije bitno, jer ga možemo sakriti naredbom Oblikovanje (Format) => Lista (Sheet) => Sakrij (Hiden)

Za definiranje imena neke liste pogledajte link Kako se imenuje popisna lista

Sam popis definiranih lista (bolovanje, slobodan dan, godišnji, praznik) izgleda ovako:

Na prvom listu "Radnik1" vodi se evidencija radnih sati za dotičnog radnika. Uočite da sam u tablicu predvidio mogućnosti za
BO - Bolovanje,  - 8 sati
GO - Godišnji odmor, - 8 sati
SD - Slobodan dan, - 0 sati
P - Praznik, - 8 sati

Ćelije u stupcu "F" formatirane su kao Vrijeme (Time) 37:30:55 jer se na taj način prikazuju rezultati u obliku vremena kao h:mm:ss

Formula za računanje sati tj. oduzimanje sati (raspon vremena između) glasi:
=IF(D2="SD";sd;IF(D2="BO";bo;IF(D2="P";p;IF(D2="GO";go;IF(D2>E2;E2+1-D2;E2-D2)))))

Što znači da će na osnovu podataka u stupcu "D" dati rezultat tj. oduzeti radne sate ili postaviti rezultat sa popisnih lista (ovisno o vrsti podatka)

Nakon popunjavanja radnih listova za svakog radnika pojedinačno, postoji radni list ZBIRNO na koji se automatski podaci sa Radnika1,2,3 linkaju i daju ukupan broj radnih sati. Naravno u ovom zbirnom listu može se dodati još nekih eventualno potrebnih podataka kao što su cijena radnog sata, ukupno bolovanja itd.

Naravno sve ovo se može smjestiti u jedan "folder" po mjesecima i na kraju napraviti zbirna knjiga cijele godine, gdje možemo proučavati koliko je koji radnik bio na GO, BO, SD i slično.

Primjer kompletne Šihterice za KLIZNO RADNO VRIJEME sa izvješćem i vođenjem radnih sati za sve djelatnike (radnike) na jednom radnom listu + izvješće za svakog radnika posebno pogledajte na ovom linku ŠIHTERICA i IZVJEŠĆE

Na slici ispod uočite da se radni sati prikazuju kao oblikovani kao BROJ ane ka vrijeme. Ovaj prikaz dobijemo tako što formulu pomnožimo sa 24. (08:00*24=8) i dobijemo decimalni broj. Naravno trebamo oblikovati ćeliju kao Brojčanu vrijednost.

Rad u tri smjene (prva smjena, druga smjena, treća smjena, noćni rad, klizno radno vrijeme)

Uočite kako izgleda izvješće o evidenciji rada i radnog vremena radnika, nakon ispisa bilo da želite Prvu,  Prvu i Drugu smjenu,  Prvu, Drugu i Treću smjenu ili Klizno radno vrijeme

PRIMJER 2.

Oduzimanje sati u Excelu

Oduzimanje radnih sati u Excelu svodi se na oduzimanje decimalnih brojeva jer Excel sate vidi kao decimalni broj.
Vrijeme sata se unosi baš kako je prikazano na slici 08:00, 16:00 (dakle sa dvotočkom)

U tablici ispod možete uočiti dva stupca, "C" sa decimalnim brojevima i "D" sa ćelijama formatiranim kao vrijeme.
U ćelijama C7 i D7 izvršeno je zbrajanje podataka u ćelijama iznad. Uočite da su rezultati zbrajanja sati isti (s malom razlikom pogleda na rezultat zbog oblikovanja (formata) ćelije.

Oduzimanje sati u ćelijama C2:C6 i D2:D6 izvedeno je sa formulom =IF(AND(A2;B2>0);IF(A2>B2;B2+1-A2;B2-A2);"")
Ovdje uočite važnost oduzimanja noćnog vremena u periodu 22:00 sati uveče do 06:00 sati ujutro (22:00-06:00)
a samo zbrajanje izvedeno je sa formulom =SUM(C2:C6)*24  i  =SUM(D2:D6) => Ova ćelija je formatirana kao 37:30:55 (vidi sliku)

PRIMJER 2a

Ako imamo situaciju da  zbroj radnih sati (vremena) želimo prikazati u tekstualnom obliku tada formatiramo ćeliju kao Custom i to na slijedeći način Custom => dd\d hh\h mm"min" (ili ovako d\d h\h mm"min").

sada nam se umjesto oblika vremena 50:15:00 vrijeme u satima u ćeliji E7 prikazuje kao 02d 02h 15min.
U slučaju da rezultat u E7 želite konvertirati samo u čiste sate tada iskoristite formulu =DAY(E7)*24+HOUR(E7) koja će vratiti rezultat 50

Ako imamo već gotov tekstualni oblik u formatu vremena kao 02d 02h 15min i želimo ga konvertirati u ispravan prikaz sati (vremena) tada konvertirajmo tekst u oblik vremena u vremenski tekst pa ga prikažimo kao vrijeme (time)

dakle u ćeliju B2 upišimo formulu koja će obraditi ćeliju A2; =MID(A2;1;1)*24+MID(A2;4;1)&":"&MID(A2;6;3) pa u ćeliju C2 upišimo formulu =VALUE(B2)

Uočite da formula u B2 oduzima tekstualne dijelove iz samog teksta i pretvara u oblik sati (zato 2d množimo sa 24) ili jednom riječju izvlači brojeve iz teksta i kreira od njih sate i minute. I na kraju u C2 pretvaramo rezultat iz B2 u vremenski oblik sa formatom Time => 37:33:55
 

Da bi vam bilo jasnije raščlanimo formulu na više dijelova koja u konačnici daje rezultat 50:15 (48h+2h)  i 15min

=MID(A2;1;1)*24 => rezultat je 48
=MID(A2;4;1) => rezultat je 2
=MID(A2;6;3) => rezultat je 15

PRIMJER 3.

Oduzimanje radnih sati

Još jedan primjer oduzimanja radnih sati (vremena)

PRIMJER 3a.

Primjer oduzimanja radnih sati u situaciji kada znamo vrijednost vremena za DOLAZAK i TRAJANJE RADA-putovanja. Trebamo izračunati kada je radnik došao na posao ili kada je krenuo na put? Uočite na slici ispod formulu za izračun kada je radnik krenuo na putovanje. =A2+60-B2 (ili =A2+1-B2 ili =A2+10-B2 ). sada će se netko pitati zašto 60 a može 10 ili 1?  Isprobajte "Evaluate Formula"

U principu bi trebalo staviti broj 1 ali sam namjerno stavio 60 i ostale primjere da shvatite što se događa kada se oduzimaju sati ovisno o prvom ili drugom broju. Ako je prvi broj veći od drugoga nije problem oduzeti sate ali ako je prvi broj manji od drugoga tada jeste.  Dan ima 24 sata i ako je početk rada 17:47 i radnik je radio 14:21 sati tada je kraj radnog vremena u 8:08 idući dan. Ako formatirate sate kao generalni broj uočit ćete decimalne brojeve za svako specifično vrijeme. Ako na to vrijeme dodate neki cijeli broj npr: 1 ili 10 tada će kod oduzimanja biti ispravno izračunata operacija u suprotnom bi otišli u minus. Nadam se da ste shvatili, ako niste surfajte i googlajte ;)

PRIMJER 4.

KAKO ZBRAJATI BROJEVE U ĆELIJAMA GDJE JE BROJ KOMBINIRAN SA TEKSTOM (zbrajanje ćelija sa tekstom)

Ako ste se ikada zapitali kako bi mogli zbrojiti brojeve u nekoj ćeliji koja sadrži tekst i broj evo mali primjer kako se može zbrajati ćelije koje sadrže kombinacije teksta i broja.

Uzmimo za primjer da radite nekakvu šihtericu za praćenje radnih sati radnika gdje ima više vrsta smjena, pa čak i rad čuvara od 12 sati. Imate nekakve smjene radnika pa želite tekstualnim opisom prikazati u svakoj ćeliji kako je dotični radnik radio taj dan. Također pogledajte Kako izdvojiti broj iz ćelije koja sadrži kombinaciju teksta i broja
 

Na slici lijevo uočite upisane radne smjene tekstualno. Da bi na kraju dobili zbroj ti radnih smjena potrebno je ukloniti tekst iz naziva i ostale znakove. Potom ostatak zbrojiti. Ovo možemo uraditi slijedećom formulom polja: =SUM(--RIGHT(A1:A5;2)) => ovo ja ARRAY formula i završavate je sa Ctrl+Shift+enter NAPOMENA: Formula ostavlja samo dva zadnja broja (znaka) ako želite kombinirati više znamenki tada umjesto 2 u formuli stavite 3 itd...(ali tada sve znamenke moraju imati tri broja (008, 012, 120, 352, 054...)

PRIMJER 5.

Dnevni sati - noćni sati (rad danju i noćni rad - Radnici i radno vrijeme)

Još jedan od primjera za izračun radnih sati (dnevni rad i noćni rad). Na slici ispod imate tri načina vođenja rada danju i rada noću (dnevni radni sati i noćni radni sati).

Uočite različite formule. Imamo stupac C u kojem računamo ukupan broj radnih sati. U stupcu D računamo rad koji spada u dnevne radne sate i stupac E u kojem računamo noćne radne sate

Ćelija A2:B7 formatirane kao TIME - 13:30
Ćelije C2:C7, D2:D7, E2:E7 su formatirane kao Custom => [h]:mm
C2 =B2-A2+(A2>B2)
D2 =(A2>B2)*MEDIAN(0;B2-1/4;2/3)+MAX(0;MIN(11/12;B2+(A2>B2))-MAX(1/4;A2))
E2 =C2-D2

Ćelije G2:G7, H2:H7 su formatirane kao Custom => [h]:mm
G2 =IF(B2<A2;IF(B2>1/4;B2-1/4;0)+IF(A2<11/12;11/12-A2;0);MIN(11/12;B2)-MAX(1/4;A2))
H2 =IF(B2<A2;MIN(B2;1/4)+(1-MAX(A2;11/12));IF(A2<1/4;1/4-A2;0)+IF(B2>11/12;B2-11/12;0))

Ćelije J2:J7, K2:K7 su formatirane kao Custom => [h]:mm
J2 =IF(B2<A2;IF(B2>1/4;B2-1/4;0)+IF(A2<11/12;11/12-A2;0);MAX(MIN(11/12;B2)-MAX(1/4;A2);0))
K2 =IF(B2<A2;MIN(B2;1/4)+(1-MAX(A2;11/12));IF(A2<1/4;MIN(1/4;B2)-A2;0)+IF(B2>11/12;B2-11/12;0))

PRIMJER 6.

Vođenje radnih sati, prekovremenih sati, dnevnih sati i noćnih sati sa pauzom za ručak (klizno radno vrijeme)

U koliko imate potrebu ili klizno radno vrijeme a uz to želite voditi vrijeme provedeno na pauzi za ručak, tada pogledajte primjer kako se može u Excelu izraditi tablica i voditi radni sati

Ćelije B3:E11 formatirane Custom => hh:mm
F3 =(E3-B3+(E3<B3))*24
G3 =((E3-B3+(E3<B3))-(D3-C3+(D3<C3)))*24
H3 =MAX(0;G3-$C$1)
I3 =E3-B3+(B3>E3)-(D3-C3)
J3 =(B3>E3)*MEDIAN(0;E3-1/4;2/3)+MAX(0;MIN(11/12;E3+(B3>E3))-MAX(1/4;B3))
K3 =IF(I3>J3;I3-J3;"")

Napomena: U tablici ispod nije riješen problem kada neki djelatnik koristi pauzu na prijelazu između dan-noć (red broj 8 "Mile" pauza od 21:30-22:30), ako znamo da je početak noćnog rada od 22:00 sati. Tako da tablica u tom redu ima grešku ako izdvajamo radne sate za Dan i radne sate za Noć. (btw: nisam to još riješio)

PRIMJER 7.

Kako prikazati koji radnik je bio na bolovanju (godišnjem, odsutan) za određeni datum ili u dotičnom mjesecu

U ovom primjeru pokazat ću kako možemo u jednom stupcu prikazati tko je sve od radnika bio na bolovanju, godišnjem ili odsutan opravdano ili neopravdano na određeni dan (današnji dan) ili pak popis svih radnika koji su tokom mjeseca bili odsutni.

Situacija je slijedeća. Imamo Šihtericu i Sheet1 na kojem vodimo evidenciju dolaska radnika na posao.

Na ovoj Šihterici imenujmo nazive raspona (Define Name) i to

A2:A9 => radnici
B2:AF9 => sati

Uočimo da su nam radnici tj. imena sortirana abecednim redom što je važno za prvi zadatak a to je

Kako prikazati tko je na današnji dan odsutan

Ovaj prikaz možemo izvršiti na istom Sheetu1 ili na drugom Sheetu2 (uočite dvije opcije)

Formule kada rezultate prikazujemo na istom Sheetu1

A1 => =TODAY()
A2 => =A2
B2 => =OFFSET($A$1;MATCH(AH2;$A$2:$A$10;1);MATCH($AH$1;$B$1:$AF$1;1))

Formule kada rezultate prikazujemo na Sheetu2

AH1 => =TODAY()
AH2 => =Sheet1!A2
AI2 => =IF(OFFSET(Sheet1!$A$1;MATCH(A2;Sheet1!$A$2:$A$10;1);MATCH($A$1;Sheet1!$B$1:$AF$1;1))="B";"B";"")

Ako želimo na Sheetu2 imati sve oznake tada uklonimo funkciju IF. Dakle ove dvije opcije iznad prikazuju status radnika za određeni datum a to je današnji datum. Naravno mi možemo po potrebi upisati datum po želji, ne moramo koristiti funkciju TODAY(). Obavezno radnici moraju biti sortirani abecednim redom uzlazno u stupcu A.

Kako prikazati tko je u toku mjeseca sve bio na bolovanju ili odsutan i koliko dana

Za ovaj primjer koristit ćemo dodatni Sheet3 na kojem ćemo linkati imena radnika i prebrojati koliko puta se pojavljuje određeni uvjet. Uvjet u našem slučaju je B=bolovanje, G=godišnji, O=opravdano i N=neopravdano

dakle uočite formule (u ovom slučaju imena radnika NE moraju biti sortirana abecednim rdom)
u stupcu A => Sheet1!=A2  => Ovim povlačimo podatke imena sa Sheeta1 iz stupca A
u stupcu B => =COUNTIF(Sheet1!B2:AF2;Sheet1!$AL$1) => Za svako ime prebrojavamo uvjet koji se nalazi u AL1 na Sheetu1

Ovdje ćemo i imenovati raspon podataka pod nazivom "count" radi kasnijeg lakšeg uklapanja u formule

Kao prvo idemo postaviti našu tablicu za pregled svih radnika i broj dana za određeni uvjet. Uočite da se u ćeliji AL1 nalazi naš uvjet a biramo ga preko postavljene Validation List.

U ćeliju AK1 postavimo naslov podataka a to je IME radnika.
U ćeliju AK2 upišimo slijedeću ARRAY formulu, formulu završavamo sa CTRL+SHIFT+ENTER. Ovdje je važno ono imenovanje raspona podataka u Šihterici na početku Primjera 7. ovog tutorijala.

{=IFERROR(INDEX(radnici;SMALL(IF((sati=$AL$1)*(COUNTIF($AK$1:AK1;radnici)=0);ROW(sati)-MIN(ROW(sati))+1;"");1));"")}

U ćeliju AL2 upišimo slijedeću formulu

=IFERROR(VLOOKUP(AK2;count;2;FALSE);"")

Kopirajmo formule prem dolje do određenog reda (broja radnika) i time smo završili zadatak. Izborom uvjeta preko padajuće liste automatski nam se prikazuje koji sve radnici zadovoljavaju uvjet za dotični mjesec i koliko dana je taj uvjet bio aktualan.

Također pogledajte novi tutorijal Šihterica u Excelu sa primjerom za download