U ovom uputstvu pokazaću vam kako možete na osnovu datuma rođenja izračunati godine starosti u Excelu, kog datuma punite N godina, malo o uslovnom formatiranju, a na youtube-u izlazi video gde se u toku rada igramo sa formulama, i pokazujemo vam ideje za vaše projekte.
Ukoliko imate neku Excel tabelu sa datumom ili godinom rođenja, a želite da dobijete podatak o godinama starosti, to možete uraditi dosta jednostavno uz pomoć Excel formule, a u ovom tekstu ću vam pokazati i kako.
Pre početka rada, proverite da li ste ispravno formatirali datume u Excelu, jer ako niste, formule neće raditi. Pročitajte tekst o formatiranju datuma u excelu - klik ovde
Kako izračunati godine starosti u Excel-u (do današnjeg dana)?
Ukoliko imate tabelu u Excel-u, gde je prikazan datum rođenja, a trebate da izračunate koliko ta osoba ima godina, pogledajte na našem primeru kako to možete uraditi. U ovom primeru smo računali koliko osoba ima godina do današnjeg datuma.
U našem primeru, u koloni A su imena, u koloni B prezimena i u koloni C datumi rođenja. U koloni D, računamo koliko godina ima osoba koja je rođena 29.2.1992, do današnjeg dana (u ovom slučaju 14.11.2021).
Kao što vidite formula za izračunavanje godina starosti je u ovoj tabeli:
=DATEDIF(C2;TODAY();"y")
U ovom slučaju koristimo funkciju DATEDIF, gde je C2 - datum rođenja, onda koristimo formulu TODAY() koja pokazuje današnji datum, kako bismo izračunali razliku između datuma rođenja i današnjeg datuma. Oznaka "y" označava da želimo da bude prikazan broj celih godina između ova dva datuma.
** U zavisnosti o Region podešavanja u Windowsu, negde se koristi tačka/zarez (;), a negde samo zarez (,) u formuli. Pogledajte upustva za podešavanje zareza u Windowsu - klikni ovde
U nastavku, u koloni E računamo koliko meseci, nakon cele godine je još ostalo do današnjeg datuma. Pa koristimo sledeću formulu:
=DATEDIF(C2;TODAY();"ym")
Pa tako dolazimo do informacije da osoba rođena 29.2.1992, na današnji dan (14.11.2021.) ima 29 punih godina i 8 punih meseci, pa nam nedostaje i o broju dana nakon x punih meseci. Za izračunavanje broja dana koristimo formulu:
=DATEDIF(C2;TODAY();"md")
Da pojasnimo ovu formulu, prvo u koloni D smo izračunali koliko osoba ima punih godina, u koloni E koliko punih meseci nakon pune godine, znači od 1992. godine + 29 punih godina je 2021. onda od 02. meseca + 8 punih meseci je 29.10.2021. sada smo izračunali koliko dana ima od 29.10.2021. do danas (14.11.2021.).
Pošto smo starost računali do današnjeg datuma, koristili smo funkciju today(), da smo hteli da računamo starost do određenog datuma uneli bi neku drugu ćeliju npr. K4.
Kako izračunati godine starosti u Excel-u (do određenog datuma)?
Ukoliko želimo da izračunamo godine starosti neke osobe, ali da ne računamo do današnjeg datuma, nego do nekog određenog datuma, npr. do 1.1.2021. ili do bilo kojeg drugog datuma koji unesemo. Potrebno je dakle izračunati razliku između dva datuma u Excel-u.
=DATEDIF(C2;$K$4;"y") - za izračunavanje broja punih godina
=DATEDIF(C2;$K$4;"ym") - za izračunavanje broja punih meseci, nakon pune godine
=DATEDIF(C2;$K$4;"md") - za izračunavanje broja punih dana, nakon punih meseci
* Primećujete da je formula ista kao i kod računanja do današnjeg datuma, s tim što umesto da ručno unosimo današnji datum u polje K4 mi smo koristili funkciju today().
** U formuli smo koristili oznaku $K$4, a ne samo K4, što znači da smo fiksirali ovo polje, a što je veoma bitno za funkcionisanje formule. Kada smo kopirali formulu u redovima ispod, excel uvek koristi podatak iz $K$4 ćelije, dok da smo u formuli koristili samo K4, kada bismo kopirali formulu u redovima ispod, Excel bi koristio podatke iz ćelija K5, 6, 7 itd.
Ranije smo pisali i kako da iz JMBG-a možete izvući godinu rođenja u Excelu, pa ako vam je dostupan samo taj dodatak, možete prvo koristiti formulu koja iz JMBG broja izvlači datum rođenja, a da onda primenjujete ove formule za izračunavanje godina starosti u Excel-u.
Kako da Excel ispiše rečenicu o godinama starosti neke osobe?
Kada smo izvukli informacije o godini, mesecima i danima starosti, onda možemo da nastavimo da se igramo u Excelu, i da npr. uz pomoć Excela automatski napišemo rečenicu koju želimo. U mom konkretnom primeru iznad, koristio sam ovu formulu:
=A2&" "&B2&" rođen/a "&TEXT(C2;"dd.mm.yyyy")&", danas "&TEXT(TODAY();"dd.mm.yyyy")&" ima "&D2&" godina, "&E2&" meseci i "&F2&" dana"
A vi se možete igrati onako kako želite, bitno je samo da ukoliko želite da napišete neki datum uvek koristite formulu Text(C2;"dd.mm.yyyy") - gde je C2 polje u kojem je datum dok je "dd.mm.yyyy" format u kojem želim da bude napisan tekst datuma.
* Za spajanje reči i vrednosti ćelija koristite znak &, s tim da uvek kada ručno pišete tekst morate da koristite navodnike, dok kada koristite vrednost neke ćelije, navodnici nisu potrebni. Npr. =A2&" "&B2&" rođen/a " - za razmak smo koristili " ", a onda smo i tekst " rođen/a " stavili pod navodnike, dok su vrednosti ćelija A2 i B2 bez navodnika
Kako izračunati kog datuma će osoba napuniti N godina?
Ako imamo datum rođenja, odredimo ćeliju gde ćemo upisati N vrednost, koja je formatirana kao broj, a onda u novoj koloni računamo kog datuma će osoba napuniti npr. 50 godina, ako znamo datum rođenja.
U koloni I, unosimo sledeću formulu za izračunvanje datuma nakon N godina.
=DATE(YEAR(C2)+$L$3;MONTH(C2);DAY(C2))
U polju L3 je podatak o broju godina, i tu možemo da menjamo broj (bitno je da je numerički fomatiran), dok u formuli koristimo fiksiranu ćeliju L3 u obliku $L$3 kako bi formula računala ispravno i u ostalim ćelijama.
Dakle, =DATE, za godinu kada će osoba proslaviti N-ti rođendan, koristimo godinu rođenja, a podatak uzimamo koristeći - year(c2), i tu dodajemo N godina, što je podatak uvek u polju $L$3. dok meseci i dani ostaju isti kada i datum rođenja, pa koristimo MONTH(C2);DAY(C2)
Ovu formulu možete koristiti u najrazličitije svrhe, pa npr. ukoliko je u polju C2 datum zasnivanja radnog odnosa u preduzeću, a u polju L3 godišnjica nakon koje sledi nagrada od preduzeća, uz pomoć ove formule možete izračunati kog datuma je N-ta godišnjica određenog radnika, a uz pomoć uslovnog formatiranja, možete obojiti sve kolone radnika koji u narednih mesec dana slave N godina rada u preduzeću.
Kako izračunati koliko godina će osoba napuniti u toku godine?
U našoj tabeli osoba "Nikola Marković rođen/a 14.12.1962, danas 14.11.2021 ima 58 godina, 11 meseci i 0 dana" međutim, do kraja godine osoba će napuniti 59 godina, pa ako nam treba podatak koliko će godina osoba napuniti određene godine, možemo koristiti sledeću formulu:
=DATEDIF(C2;DATE(YEAR(TODAY());12;31);"y")
Znači računa broj godina od datuma rođenja do kraja godine koje želimo, pa zato za određivanje kraja godine koristimo formulu DATE, gde za godinu koristimo današnji datum TODAY(), ali to može biti i specifični datum, kada ručno unosimo kao npr. $K$4 u gornjem primeru. dok za mesec koristimo 12, i dan 31, jer je to poslednji dan u bilo kojoj godini.
Kako obojiti sve kolone u kojima neka osoba ima manje ili više od određenog broja godina?
Uslovno formatiranje tj. Conditional Formating je jedan fenomenalan alat u Excelu, koji će nam u ovom konkretnom primeru omogućiti da određene starosne grupe obojimo različitom bojom.
Prvo, selektujte celu tabelu (osim naziva kolona), što je u ovom slučaju $A$2:$H$16, pa idite na opciju "Conditional Formating" - "New rule" pa u prozoru "Select a rule Type" izaberite "Use a formula to determine which cells to format".
Klikom na dugme "Format" birate kako ćelije koje ispunjavaju određen uslov trebaju biti formatirane (ja sam ovde iz kartice Fill samo izabrao boju, a vi se možete igrati i boji fontova itd.).
U polju "Format values where this formula is true:" sam unosio sledeće formule:
=$D2>45 - za svetlo plavu,
=AND($D2>30;$D2<=45) - za svetlo žutu i
=AND($D2<=30;$D2>=18) - za svetlo zelenu.
Što znači da sam 3x ponavljao ovaj postupak, samo što sam u Format-u birao drugu boju i u polju za unos formule koristio drugu formulu.
Pogledajte i Youtube video:
0 Коментари