Bendroji formulė
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Santrauka
Jei jums reikia konvertuoti datą į „Julian“ datos formatą programoje „Excel“, galite tai padaryti sukurdami formulę, kurioje naudojamos funkcijos TEKSTAS, METAI ir DATA.
Fonas
„Julian date format“ reiškia formatą, kai datos metų vertė sujungiama su „tų metų eilės diena“ (ty 14 diena, 100 diena ir kt.), Kad būtų suformuotas datos antspaudas.
Yra keletas variantų. Šio formato data gali apimti 4 skaitmenų metus (yyyy) arba dviejų skaitmenų metus (yy), o dienos numeris gali būti užpildytas nuliais arba ne, kad visada būtų naudojami 3 skaitmenys. Pavyzdžiui, 2017 m. Sausio 21 d. Galite pamatyti:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Sprendimas
Dviejų skaitmenų metams + dienos skaičiui nenaudojant užpildo:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Dviejų skaitmenų metams + dienos numeriui, pažymėtam nuliais iki 3 vietų:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Keturių skaitmenų metams + dienos numeriui pridedamas nulis iki 3 vietų:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Paaiškinimas
Ši formulė galutinį rezultatą sudaro 2 dalys, sujungtos susiejant su „&“ (&) operatoriumi.
Kairėje raidės raidėje sugeneruojame metų vertę. Norėdami išgauti 2 skaitmenų metus, galime naudoti TEXT funkciją, kuri formulėje gali pritaikyti skaičių formatą:
TEXT(B5,"yy")
Norėdami išgauti visus metus, naudokite YEAR funkciją:
YEAR(B5)
Dešinėje ženklo pusėje turime išsiaiškinti metų dieną. Tai darome atimdami paskutinę praėjusių metų dieną nuo datos, su kuria dirbame. Kadangi datos yra tik serijos numeriai, tai suteiks mums „n-ąją“ metų dieną.
Norėdami gauti paskutinę praėjusių metų metų dieną, naudojame DATE funkciją. Kai nurodote DATE metų ir mėnesio vertę, o dieną - nulį, gausite paskutinę praėjusio mėnesio dieną. Taigi:
B5-DATE(YEAR(B5),1,0)
pateikia paskutinę praėjusių metų dieną, kuri pavyzdyje yra 2015 m. gruodžio 31 d.
Dabar dienos vertę turime pažymėti nuliais. Vėlgi, mes galime naudoti TEXT funkciją:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Grįžtamoji Juliano data
Jei jums reikia konvertuoti Julian datą atgal į įprastą datą, galite naudoti formulę, kuri analizuoja Julian datą ir paleidžia ją per datos funkciją su mėnesiu 1 ir diena, lygi „n-tai“ dienai. Pvz., Tai sukurs datą nuo Julian datos, tokios kaip 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Jei turite tik dienos numerį (pvz., 100, 153 ir kt.), Galite užkoduoti metus ir įterpti dieną:
=DATE(2016,1,A1)
Kur A1 yra dienos numeris. Tai veikia, nes DATE funkcija žino, kaip koreguoti reikšmes, kurios yra už diapazono ribų.
