Vispārēja formula
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Kopsavilkums
Ja programmā Excel datums jāpārvērš par Juliāna datuma formātu, to var izdarīt, izveidojot formulu, kurā tiek izmantotas funkcijas TEKSTS, GADS un DATUMS.
Priekšvēsture
"Džuliana datuma formāts" attiecas uz formātu, kurā datuma gada vērtību apvieno ar "attiecīgā gada kārtas dienu" (ti, 14. diena, 100. diena utt.), Lai izveidotu datuma zīmogu.
Ir vairākas variācijas. Šajā formātā datums var ietvert četrciparu gadu (gggg) vai divciparu gadu (ggg), un dienas numuru var papildināt ar nullēm, lai vienmēr izmantotu 3 ciparus. Piemēram, 2017. gada 21. janvārī jūs varat redzēt:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Risinājums
Divciparu gadam + dienas skaitlis bez pildījuma izmantošanas:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Divciparu gadam + dienas skaitlis, kas 3 vietās ierakstīts ar nullēm:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Četrciparu gadam + dienas skaitlis, kas 3 vietās ierakstīts ar nullēm:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Paskaidrojums
Šī formula gala rezultātu veido 2 daļās, apvienojot to ar konkūra (&) operatoru.
Ampersanda kreisajā pusē mēs ģenerējam gada vērtību. Lai izgūtu divciparu gadu, mēs varam izmantot funkciju TEXT, kas formulas iekšienē var izmantot ciparu formātu:
TEXT(B5,"yy")
Lai iegūtu visu gadu, izmantojiet funkciju YEAR:
YEAR(B5)
Ampersanda labajā pusē mums ir jāizdomā gada diena. Mēs to darām, no datuma, ar kuru mēs strādājam, atņemot iepriekšējā gada pēdējo dienu. Tā kā datumi ir tikai sērijas numuri, tas mums piešķirs gada "n-to" dienu.
Lai iegūtu iepriekšējā gada pēdējā gada dienu, mēs izmantojam funkciju DATE. Piešķirot DATE gada un mēneša vērtību un nullei dienai, tiek iegūta iepriekšējā mēneša pēdējā diena. Tātad:
B5-DATE(YEAR(B5),1,0)
dod mums iepriekšējā gada pēdējo dienu, kas piemērā ir 2015. gada 31. decembris.
Tagad mums ir jāievada dienas vērtība ar nullēm. Atkal mēs varam izmantot funkciju TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Reverss Džuliana datums
Ja jums ir jākonvertē Juliāna datums atpakaļ uz parastu datumu, varat izmantot formulu, kas parsē Džuliana datumu un palaiž to cauri datuma funkcijai ar mēnesi 1 un dienu, kas vienāda ar "n-to" dienu. Piemēram, tas izveidotu datumu no Julian datuma, piemēram, 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ja jums vienkārši ir dienas numurs (piemēram, 100, 153 utt.), Jūs varat kodēt gadu un dienu ievietot šādi:
=DATE(2016,1,A1)
Kur A1 ir dienas numurs. Tas darbojas, jo funkcija DATE zina, kā pielāgoties vērtībām, kas ir ārpus diapazona.
