Yleinen kaava
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Yhteenveto
Jos haluat muuntaa päivämäärän Julian-päivämäärämuodoksi Excelissä, voit tehdä sen rakentamalla kaavan, joka käyttää TEKSTI-, VUOSI- ja PÄIVÄYS-funktioita.
Tausta
"Julian-päivämäärämuoto" viittaa muotoon, jossa päivämäärän vuosiarvo yhdistetään "kyseisen vuoden järjestyspäivään" (eli 14. päivä, 100. päivä jne.) Päivämääräleiman muodostamiseksi.
Vaihtoehtoja on useita. Päivämäärä tässä muodossa voi sisältää nelinumeroisen vuoden (vvvv) tai kaksinumeroisen vuoden (vv), ja päiväyksen numero voidaan täyttää nollilla, jotta aina käytetään 3 numeroa. Esimerkiksi päivämäärältä 21. tammikuuta 2017 saatat nähdä:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Ratkaisu
Kaksinumeroinen vuosi + päiväluku ilman täytettä:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Kaksinumeroinen vuosi + päivät, jotka on täynnä nollia 3 paikkaan:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Nelinumeroinen vuosi + päivät, jotka on täytetty nollilla 3 paikkaan:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Selitys
Tämä kaava rakentaa lopputuloksen kahteen osaan yhdistettynä ketjutuksella ampersand (&) -operaattorilla.
Ampersandin vasemmalla puolella luodaan vuosiarvo. Kaksinumeroisen vuoden purkamiseksi voimme käyttää TEXT-funktiota, joka voi soveltaa lukumuotoa kaavan sisällä:
TEXT(B5,"yy")
Voit poimia koko vuoden käyttämällä VUOSI-toimintoa:
YEAR(B5)
Ampersandin oikealla puolella on selvitettävä vuoden päivä. Teemme tämän vähentämällä edellisen vuoden viimeisen päivän työskentelypäivästä. Koska päivämäärät ovat vain sarjanumeroita, tämä antaa meille vuoden "n." Päivän.
Saadaksesi edellisen vuoden viimeisen päivän käytämme PÄIVÄYS-funktiota. Kun annat päivämäärälle DATE vuoden ja kuukauden arvon ja nollan päivälle, saat edellisen kuukauden viimeisen päivän. Niin:
B5-DATE(YEAR(B5),1,0)
antaa meille edellisen vuoden viimeisen päivän, joka on esimerkissä 31. joulukuuta 2015.
Nyt meidän on täytettävä päiväarvo nollilla. Jälleen voimme käyttää TEXT-toimintoa:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Käänteinen Julian-päiväys
Jos haluat muuntaa Julian-päivämäärän takaisin normaaliksi päivämääräksi, voit käyttää kaavaa, joka jäsentää Julian-päivämäärän ja suorittaa sen päivämääräfunktion läpi. Kuukausi on 1 ja päivä, joka on sama kuin "n." Päivä. Esimerkiksi tämä luo päivämäärän vvvvp Julian -päivästä, kuten 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Jos sinulla on vain päivänumero (esim. 100, 153 jne.), Voit koodata vuoden kovasti ja lisätä päivä tämän:
=DATE(2016,1,A1)
Missä A1 sisältää päivän numeron. Tämä toimii, koska PÄIVÄYS-toiminto tietää, kuinka sopeutua arvoihin, jotka ovat alueen ulkopuolella.
