Generička formula
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Sažetak
Ako trebate datum pretvoriti u julijanski format datuma u Excelu, to možete učiniti izgradnjom formule koja koristi funkcije TEKST, GODINA i DATUM.
Pozadina
"Julijanski format datuma" odnosi se na format u kojem se godišnja vrijednost datuma kombinira s "rednim danom za tu godinu" (tj. 14. dan, 100. dan, itd.) Kako bi se oblikovao datumski pečat.
Postoji nekoliko varijacija. Datum u ovom formatu može sadržavati četveroznamenkastu godinu (yyyy) ili dvoznamenkastu godinu (yy), a broj dana može biti ali i ne mora biti nadopunjen nulama da bi se uvijek koristile 3 znamenke. Na primjer, za datum 21. siječnja 2017. mogli biste vidjeti:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Riješenje
Za dvoznamenkastu godinu + dnevni broj bez popunjavanja:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Za dvoznamenkastu godinu + dnevni broj podstavljen nulama na 3 mjesta:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Za četveroznamenkastu godinu + dnevni broj podstavljen nulama na 3 mjesta:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Obrazloženje
Ova formula gradi konačni rezultat u 2 dijela, pridružena spajanjem s operatorom ampersand (&).
S lijeve strane ampersanda generiramo vrijednost godine. Da bismo izdvojili dvoznamenkastu godinu, možemo upotrijebiti funkciju TEXT koja može primijeniti format broja unutar formule:
TEXT(B5,"yy")
Za izdvajanje pune godine koristite funkciju GODINA:
YEAR(B5)
Na desnoj strani ampersanda moramo shvatiti dan u godini. To radimo oduzimajući zadnji dan prethodne godine od datuma s kojim radimo. Budući da su datumi samo serijski brojevi, to će nam dati "n-ti" dan u godini.
Da bismo dobili zadnji dan u godini prethodne godine, koristimo funkciju DATUM. Kada date DATE vrijednost za godinu i mjesec, a nulu za dan, dobit ćete zadnji dan prethodnog mjeseca. Tako:
B5-DATE(YEAR(B5),1,0)
daje nam zadnji dan prethodne godine, a to je 31. prosinca 2015. u primjeru.
Sada trebamo vrijednost dana podmetnuti nulama. Opet, možemo koristiti funkciju TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Obrnuti julijanski datum
Ako trebate pretvoriti julijanski datum u redoviti datum, možete upotrijebiti formulu koja raščlanjuje julijanski datum i provodi ga kroz datumsku funkciju s mjesecom 1 i danom koji je jednak "n-tom" danu. Na primjer, ovo bi stvorilo datum od julijanskog datuma yyyyddd poput 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ako imate samo broj dana (npr. 100, 153, itd.), Možete čvrsto kodirati godinu i umetnuti dan ovako:
=DATE(2016,1,A1)
Gdje A1 sadrži broj dana. To djeluje jer se funkcija DATE zna prilagoditi za vrijednosti koje su izvan dosega.
