V članku je uporabljen Excel 2016 verzije 16.0.4966.1000.

Problem, s katerim se pogosto srečamo pri sestavljanju koledarske tabele, so prazniki. Ti so različni v različnih državah in pogosto niso fiksni. V tem članku si bomo ogledali, kako v Excel-u sestavimo podroben poslovni koledar. Sestavili bomo koledar, ki vključuje:

• Polje Datum, ki zajema vse datume od 1.1.2010 do 1.1.2030
• Polje Vikend, ki označuje ali gre za vikend (1/0)
• Polje Praznik, ki označuje ali gre za praznik (1/0)
• Polje Ime praznika, ki označuje za kateri praznik gre
• Polje Dela prost praznik, ki označuje ali gre za dela prost praznik (1/0)
• Polje Delavnik ki označuje ali gre za delovni dan ali ne (1/0)

Koledar, ki smo ga sestavili, je dostopen kot Excel datoteka na naslednji  povezavi: Excel koledar 2020-2030 s prazniki.

Sestava koledarja

Koledar bomo ustvarili z Excel-om. Odpremo prazno datoteko, poimenujemo zavihek Koledar in vnesemo imena polj Datum, Vikend, Praznik, Ime praznika, Dela prost praznik, Delavnik.

Polje Datum

V polje Datum vpišemo začetni datum in končni datum, 1.1.2010 in 1.1.2030.

Spremenimo tip podatka v število.

Drugo število si zabeležimo in izbrišemo. Prvo število izberemo in na kartici Editing izberemo Fill > Series.

Izberemo serijo tipa Columns in Linear, korak oz. Step value 1 in končno vrednost oz. Stop value 47484.

Dobimo zaporedje vseh števil med začetnim in končnim številom 40179 in 47484.

Tip podatka spremenimo v datum. Dobimo zaporedje vseh datumov med začetnim datumom 1.1.2010 in končnim datumom 1.1.2030.

Polje Vikend

V polje Vikend vnesemo formulo, ki izpiše vrednost 1 za dneve v vikendu (sobota in nedelja), ter 0 za ostale dneve. Uporabimo naslednjo formulo.

=IF(OR(WEEKDAY(A2;2)=6;WEEKDAY(A2;2)=7);1;0)

Polje Praznik

Excel nima funkcije, ki bi znala določiti praznike, ampak jih moramo podati kot seznam. V Sloveniji poznamo naslednje praznike.

Državni prazniki

• 1. in 2. januar: novo leto
• 8. februar: Prešernov dan, slovenski kulturni praznik
• 27. april: dan upora proti okupatorju
• 1. in 2. maj: praznik dela
• 8. junij: dan Primoža Trubarja*
• 25. junij: dan državnosti
• 17. avgust: združitev prekmurskih Slovencev z matičnim narodom*
• 15. september: vrnitev Primorske k matični domovini*
• 23. september: dan slovenskega športa*
• 25. oktober: dan suverenosti*
• 1. november: dan spomina na mrtve
• 23. november: dan Rudolfa Maistra*
• 26. december: dan samostojnosti in enotnosti

* Ni dela prost dan.

Drugi dela prosti dnevi

• velikonočna nedelja, velikonočni ponedeljek
• binkoštna nedelja – binkošti
• 15. avgust – Marijino vnebovzetje
• 31. oktober – dan reformacije
• 25. december – božič

Vir: https://www.gov.si/teme/drzavni-prazniki-in-dela-prosti-dnevi/

Da ohranimo enostaven pregled, bomo vse naštete dneve obravnavali kot praznik.

Fiksni prazniki

Vsi prazniki razen velikonočnega ponedeljka in binkoštne nedelje imajo fiksen datum.

Ustvarimo nov zavihek, ga poimenujemo Prazniki in vnesemo polja Dan, Mesec, Ime praznika in Dela prosto. Polje Dela prosto izpolnimo z 1 za praznike ki so dela prosti in 0 za ostale.

Z desnim klikom na stolpec C vstavimo nov stolpec z izbiro Vstavi. Stolpec poimenujemo DanMesec in uporabimo spodnjo formulo.

=(IF(LEN(A2)<2;0&A2;A2)&IF(LEN(B2)<2;0&B2;B2))

Dobili smo enoličen zapis dneva in meseca posameznega praznika, ki ga bomo uporabili pri določanju praznikov na zavihku Koledar.

Na zavihku Koledar zdaj dodamo nov prazen stolpec in ga poimenujemo Fiksni prazniki.

V polje C2 nato vnesemo naslednjo formulo in jo zapolnimo za vse datume.

=IFERROR(VLOOKUP((IF(LEN(DAY($A2))<2;0&DAY($A2);DAY($A2))&IF(LEN(MONTH($A2))<2;0&MONTH($A2);MONTH($A2))); Prazniki[[#All];[DanMesec]:[Dela prosto]]; 3;FALSE); “”)

Formula določi enoličen zapis dneva in meseca ter iz tabele na zavihku Fiksni prazniki prebere ime praznika.

Plavajoči prazniki

Trije prazniki so plavajoči:

• velikonočna nedelja, velikonočni ponedeljek
• binkoštna nedelja

Ker je velikonočna nedelja zagotovo dela prosta, jo bomo spustili, in dodali praznikom zgolj velikonočni ponedeljek in binkoštno nedeljo.

Tabeli vstavimo dve novi polji: Velikonočni ponedeljek in Binkoštna nedelja.

V polje D2 vnesemo naslednjo formulo in jo zapolnimo za vse datume.

=IF(FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&”/”&”5/”&YEAR(A2);7)-34+1=A2;$D$1;””)

V polje E2 vnesemo naslednjo formulo in jo zapolnimo za vse datume.

=IF(FLOOR(DAY(MINUTE(YEAR(A2)/38)/2+56)&”/”&”5/”&YEAR(A2);7)-34+1+50-2=A2;$E$1;””)

Formuli za prave datume izpišeta ime praznika, ostala polja pa pustita prazna.

Polje Praznik

V polje Praznik zdaj želimo izpisati vrednost 1 če katerokoli od polj C, D in E vsebuje praznik, sicer pa 0. V polje G2 tako vnesemo formulo, ki preveri če je katera od celic C, D in E neprazna, ter v tem primeru izpiše 1, sicer pa 0.

=IF(C2<>””;1;IF(D2<>””;1;IF(E2<>””;1; 0)))

Polje Ime praznika

V polje Ime praznika zdaj želimo izpisati vrednost polj C, D in E. V polje G2 tako vnesemo formulo, ki preveri če je katera od celic C, D in E neprazna, ter izpiše njeno vrednost.

=IF(C2<>””;C2;IF(D2<>””;D2;IF(E2<>””;E2; “”)))

Zdaj lahko polja C, D in E skrijemo: stolpce označimo in z desnim klikom izberemo Skrij.

Polje Dela prost praznik

Da določimo dela proste praznike bomo najprej ločeno določili dela proste fiksne in plavajoče praznike. Dodamo dva nova stolpca: Dela prost fiksni praznik in Dela prost plavajoči praznik.

V polje H2 vnesemo formulo, ki z zavihka Fiksni prazniki prebere kateri prazniki so dela prosti in vrne 1, sicer pa 0.

=IFERROR(VLOOKUP((IF(LEN(DAY($A2))<2;0&DAY($A2);DAY($A2))&IF(LEN(MONTH($A2))<2;0&MONTH($A2);MONTH($A2))); Prazniki[[#All];[DanMesec]:[Dela prosto]]; 4;FALSE); 0)

V polje I2 vnesemo formulo, ki za katerikoli od plavajočih dveh praznikov vrne 1, saj sta oba dela prosta dneva. V nasprotnem primeru vrne 0.

=IF(OR(D2<>””;E2<>””);1;0)

V polje Dela prost praznik zdaj vnesemo formulo ki oba dodana stolpca preveri in izpiše 1 za dela proste praznike in 0 za ostale.

=IF(OR(H2=1;I2=1);1;0)

Polji Dela prost fiksni praznik in Dela prost plavajoči praznik zdaj skrijemo z izbiro obeh stolpcev in desnim klikom na Skrij.

Delavnik

Izpolnimo še zadnje polje, v celico K2 vnesemo formulo, ki preveri fiksne in plavajoče praznike ter izpiše 1 ali 0.

=IF(OR(B2=1;H2=1); 0;1)