Általános képlet
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Összegzés
Ha egy dátumot konvertálni kell Julian dátumformátumra az Excel programban, akkor ezt megteheti egy olyan képlet létrehozásával, amely a SZÖVEG, ÉV és DÁT függvényeket használja.
Háttér
A "Julián dátumformátum" olyan formátumra utal, ahol a dátum évértékét az "adott év rendes napjával" (azaz 14. nap, 100. nap stb.) Kombinálva dátumbélyegző képződik.
Számos variáció létezik. Az ebben a formátumban szereplő dátum tartalmazhat egy négyjegyű évet (éééé) vagy egy kétjegyű évet (éé), és a napszámot nullával lehet kitölteni, vagy sem, hogy mindig 3 számjegyet használjon. Például a 2017. január 21-i dátumot láthatja:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Megoldás
Kétjegyű év + napszám párnázás nélkül:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Kétjegyű év + napszám 3 helyre nullával kiegészítve:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Négyjegyű év + napszám 3 helyre nullával kiegészítve:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Magyarázat
Ez a képlet 2 részben építi fel a végeredményt, összekapcsolva az ampersand (&) operátorral.
Az amszter bal oldalán generáljuk az év értékét. 2 számjegyű év kivonásához használhatjuk a TEXT függvényt, amely számformátumot alkalmazhat a képleten belül:
TEXT(B5,"yy")
A teljes év kivonásához használja az ÉV függvényt:
YEAR(B5)
Az ampersand jobb oldalán ki kell találnunk az év napját. Ezt úgy tesszük, hogy kivonjuk az előző év utolsó napját abból a dátumból, amellyel dolgozunk. Mivel a dátumok csak sorozatszámok, ez adja az év "n-edik" napját.
Az előző év utolsó napjának megszerzéséhez használjuk a DÁTUM funkciót. Amikor a DATE-nak ad egy év és hónap értéket, és egy nullát a napra, akkor az előző hónap utolsó napját kapja. Így:
B5-DATE(YEAR(B5),1,0)
megadja az előző év utolsó napját, amely a példában 2015. december 31-e.
Most nullákkal kell kitölteni a napi értéket. Ismét használhatjuk a TEXT funkciót:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Fordított Julián dátum
Ha vissza kell állítania egy Julián dátumot normál dátumra, használhat egy képletet, amely elemzi a Julián dátumot, és végigfuttatja a dátumfüggvényt egy hónap 1-vel és az "n-edik" nappal megegyező nappal. Például ez létrehozna egy dátumot egy éééé pp Julianus dátumból, például 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ha csak napi száma van (pl. 100, 153 stb.), Akkor keményen beírhatja az évet, és ezt beillesztheti a napba:
=DATE(2016,1,A1)
Ahol az A1 tartalmazza a napszámot. Ez azért működik, mert a DATE függvény tudja, hogyan kell beállítani a tartományon kívül eső értékekhez.
