Генеричка формула
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Резиме
Ако требате да претворите датум у јулијански формат датума у програму Екцел, то можете учинити изградњом формуле која користи функције ТЕКСТ, ГОДИНА и ДАТУМ.
Позадина
„Јулијански формат датума“ односи се на формат где се годишња вредност датума комбинује са „редним даном за ту годину“ (тј. 14. дан, 100. дан итд.) Да би се формирао печат датума.
Постоји неколико варијација. Датум у овом формату може да садржи четвороцифрену годину (ииии) или двоцифрену годину (ии), а број дана може или не мора бити подвучен нулама да би се увек користиле 3 цифре. На пример, за датум 21. јануар 2017. можда ћете видети:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Решење
За двоцифрену годину + дневни број без додавања:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
За двоцифрену годину + дневни број подвучен нулама на 3 места:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
За четвороцифрену годину + дневни број подвучен нулама на 3 места:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Објашњење
Ова формула гради коначни резултат из 2 дела, удружена спајањем са знаком (&).
Лево од амперсанда генеришемо вредност године. Да бисмо издвојили двоцифрену годину, можемо да користимо функцију ТЕКСТ која може применити формат броја унутар формуле:
TEXT(B5,"yy")
Да бисте издвојили целу годину, користите функцију ИЕАР:
YEAR(B5)
На десној страни амперсанда морамо да утврдимо дан у години. То радимо одузимањем последњег дана претходне године од датума са којим радимо. Будући да су датуми само серијски бројеви, ово ће нам дати „н-ти“ дан у години.
Да бисмо добили последњи дан у години претходне године, користимо функцију ДАТУМ. Када ДАТЕ дате вредност за годину и месец и нулу за дан, добићете последњи дан претходног месеца. Тако:
B5-DATE(YEAR(B5),1,0)
даје нам последњи дан претходне године, а то је 31. децембар 2015. у примеру.
Сада дневну вредност морамо подметнути нулама. Поново можемо да користимо функцију ТЕКСТ:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Обрнути јулијански датум
Ако требате да претворите јулијански датум у редован датум, можете да користите формулу која рашчлањује јулијански датум и проводи га кроз функцију датума са месецом од 1 и даном који је једнак „н-том“ дану. На пример, ово би створило датум од јулијанског датума ииииддд попут 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ако имате само број дана (нпр. 100, 153, итд.), Годину можете чврсто кодирати и уметнути дан:
=DATE(2016,1,A1)
Тамо где А1 садржи број дана. Ово функционише јер функција ДАТЕ зна како да се прилагоди за вредности које су изван опсега.
