Загальна формула
=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 date, наприклад 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Якщо у вас є просто номер дня (наприклад, 100, 153 тощо), ви можете вказати рік із жорстким кодом і вставити наступний день:
=DATE(2016,1,A1)
Де А1 містить номер дня. Це працює, оскільки функція DATE знає, як налаштувати значення, що виходять за межі діапазону.
