Общая формула
=YEAR(date)&TEXT(date-DATE(YEAR(date),1,0),"000")
Резюме
Если вам нужно преобразовать дату в юлианский формат даты в Excel, вы можете сделать это, создав формулу, которая использует функции ТЕКСТ, ГОД и ДАТА.
Задний план
«Юлианский формат даты» относится к формату, в котором годовое значение даты комбинируется с «порядковым днем этого года» (например, 14-й день, 100-й день и т. Д.) Для формирования штампа даты.
Есть несколько вариантов. Дата в этом формате может включать 4-значный год (гггг) или год из 2-х цифр (гг), а номер дня может быть дополнен или не дополнен нулями, чтобы всегда использовать 3 цифры. Например, для даты 21 января 2017 г. вы можете увидеть:
1721 // YYD 201721 //YYYYD 2017021 // YYYYDDD
Решение
Для года с двумя цифрами + номер дня без заполнения используйте:
=TEXT(B5,"yy")&B5-DATE(YEAR(B5),1,0)
Для года с двумя цифрами + номер дня, дополненный нулями до трех знаков:
=TEXT(B5,"yy")&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Для года из четырех цифр + номер дня, дополненный нулями до трех знаков:
=YEAR(B5)&TEXT(B5-DATE(YEAR(B5),1,0),"000")
Объяснение
Эта формула строит окончательный результат из 2 частей, соединенных конкатенацией с оператором амперсанда (&).
Слева от амперсанда мы генерируем значение года. Чтобы извлечь год из 2 цифр, мы можем использовать функцию ТЕКСТ, которая может применять числовой формат внутри формулы:
TEXT(B5,"yy")
Чтобы извлечь полный год, используйте функцию ГОД:
YEAR(B5)
Справа от амперсанда нам нужно вычислить день года. Мы делаем это, вычитая последний день предыдущего года из даты, с которой мы работаем. Поскольку даты - это просто порядковые номера, это даст нам «n-й» день года.
Чтобы получить последний день года предыдущего года, мы используем функцию ДАТА. Когда вы задаете DATE значение года и месяца и ноль для дня, вы получаете последний день предыдущего месяца. Так:
B5-DATE(YEAR(B5),1,0)
дает нам последний день прошлого года, которым в примере является 31 декабря 2015 года.
Теперь нам нужно дополнить дневное значение нулями. Опять же, мы можем использовать функцию ТЕКСТ:
TEXT(B5-DATE(YEAR(B5),1,0),"000")
Обратная юлианская дата
Если вам нужно преобразовать дату по юлианскому календарю обратно в обычную дату, вы можете использовать формулу, которая анализирует дату по юлианскому календарю и запускает ее через функцию даты с месяцем, равным 1, и днем, равным «n-му» дню. Например, это создаст дату из юлианской даты yyyyddd, например, 1999143.
=DATE(LEFT(A1,4),1,RIGHT(A1,3)) // for yyyyddd
Если у вас просто номер дня (например, 100, 153 и т. Д.), Вы можете жестко запрограммировать год и вставить следующий день:
=DATE(2016,1,A1)
Где A1 содержит номер дня. Это работает, потому что функция ДАТА знает, как корректировать значения, выходящие за пределы допустимого диапазона.
