Splošna formula
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Povzetek
Če morate datum pretvoriti v julijansko obliko zapisa datuma v Excelu, lahko to naredite tako, da sestavite formulo, ki uporablja funkcije TEXT, YEAR in DATE.
Ozadje
"Julijska oblika zapisa datuma" se nanaša na obliko, pri kateri se letna vrednost datuma kombinira z "rednim dnevom za to leto" (tj. 14. dan, 100. dan itd.), Da se oblikuje datumski žig.
Obstaja več različic. Datum v tej obliki lahko vključuje štirimestno leto (llll) ali dvomestno leto (yy), številka dneva pa je lahko ali pa tudi ne sme biti podprt z ničlami, da bo vedno uporabila 3 števke. Na primer, za datum 21. januar 2017 boste morda videli:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Rešitev
Za dvomestno leto + številko dneva brez uporabe oblazinjenja:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Za dvomestno leto + dnevna številka z ničlami na 3 mesta:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Za štirimestno leto + dnevna številka, podpolnjena z ničlami na 3 mesta:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Pojasnilo
Ta formula gradi končni rezultat v dveh delih, ki sta združeni z združevanjem z operatorjem ampersand (&).
Na levi strani ampersanda ustvarimo letno vrednost. Za ekstrakcijo dvomestnega leta lahko uporabimo funkcijo TEXT, ki lahko uporabi formulo števila znotraj formule:
TEXT(B5,"yy")
Če želite izvleči celo leto, uporabite funkcijo YEAR:
YEAR(B5)
Na desni strani ampersanda moramo ugotoviti dan v letu. To storimo tako, da od dneva, ko delamo, odštejemo zadnji dan preteklega leta. Ker so datumi le serijske številke, bomo s tem dobili "n-ti" dan v letu.
Za pridobitev zadnjega dne v letu prejšnjega leta uporabimo funkcijo DATUM. Ko DATE navedete vrednost za leto in mesec in nič za dan, dobite zadnji dan prejšnjega meseca. Torej:
B5-DATE(YEAR(B5),1,0)
nam daje zadnji dan preteklega leta, to je v primeru 31. decembra 2015.
Zdaj moramo vrednost dneva zapolniti z ničlami. Spet lahko uporabimo funkcijo TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Povratni julijanski datum
Če morate julijski datum pretvoriti nazaj v običajni datum, lahko uporabite formulo, ki razčleni julijanski datum in ga požene skozi funkcijo datuma z mesecem 1 in dnevom, ki je enak "n-temu" dnevu. Tako bi na primer ustvarili datum iz julijskega datuma yyyyddd, kot je 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Če imate samo številko dneva (npr. 100, 153 itd.), Lahko leto trdo kodirate in dan vstavite tako:
=DATE(2016,1,A1)
Kjer A1 vsebuje številko dneva. To deluje, ker se funkcija DATE zna prilagoditi za vrednosti, ki so izven obsega.
