Обща формула
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Обобщение
Ако трябва да конвертирате дата в юлиански формат за дата в Excel, можете да го направите, като изградите формула, която използва функциите TEXT, YEAR и DATE.
Заден план
„Юлиански формат на датата“ се отнася до формат, при който годишната стойност на датата се комбинира с „поредния ден за тази година“ (т.е. 14-ия ден, 100-ия ден и т.н.), за да образува печат на датата.
Има няколко вариации. Датата в този формат може да включва 4-цифрена година (гггг) или двуцифрена година (ггг), а номерът на деня може или не може да бъде подплатен с нули, за да се използват винаги 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, която може да приложи числов формат във формула:
TEXT(B5,"yy")
За да извлечете цяла година, използвайте функцията YEAR:
YEAR(B5)
От дясната страна на амперсандата трябва да разберем деня от годината. Правим това, като изваждаме последния ден от предходната година от датата, с която работим. Тъй като датите са само серийни номера, това ще ни даде "n-ия" ден от годината.
За да получим последния ден от годината на предходната година, използваме функцията DATE. Когато дадете на DATE стойност за година и месец и нула за деня, получавате последния ден от предходния месец. Така:
B5-DATE(YEAR(B5),1,0)
ни дава последния ден от предходната година, който е 31 декември 2015 г. в примера.
Сега трябва да запълним дневната стойност с нули. Отново можем да използваме функцията TEXT:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Обратна дата на Джулиан
Ако трябва да конвертирате юлианска дата обратно в обикновена дата, можете да използвате формула, която анализира юлианска дата и я прекарва през функцията за дата с месец 1 и ден, равен на "n-тия" ден. Например, това би създало дата от yyyyddd Julian дата като 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Ако имате само номер на ден (например 100, 153 и т.н.), можете да кодирате твърдо годината и да вмъкнете деня това:
=DATE(2016,1,A1)
Където А1 съдържа номера на деня. Това работи, защото функцията DATE знае как да коригира стойности, които са извън обхвата.
