Пример формулы перевода григорианской даты в юлианскую в Excel
Юлианские даты в формате (JD) часто используются в промышленности как единое значение времени и партии товара. Данный способ кодирования времени предоставляет возможность для продавцов, клиентов и работников обслуживания проверять, когда был произведен данный продукт, какой его срок и из какой он партии товаров вышел на рынок. Юлианские даты также активно используются в программировании, армии, астрономии и др. В данном примере мы научимся делать перевод юлианских дат в григорианские и обратно. В результате создадим в Excel конвертер юлианской и григорианской даты.
Как сделать перевод григорианской даты в юлианскую в Excel
В разных сферах промышленности применяются разные отличия юлианских дат, однако наиболее часто используется формат, который состоит из двух частей:
- Двузначное числовое значение года.
- Порядковый номер дня в этом же году.
Например, для даты 1960-01-01 соответствует юлианская дата – 601, а для даты 2014-12-31 будет соответствовать юлианская дата – 14365.
В Excel нет встроенных функций чтобы выполнить перевод григорианской даты в юлианскую. Однако ниже на рисунке изображено решение реализовано с помощью формулы, позволяющей получить необходимый результат:
Как видно на рисунке данная формула состоит из двух частей, соединенных между собой символом амперсант (&):
- Первая часть использует текстовую функцию ПРАВСИМВ. Она позволяет вырезать последние 2-е цифры года. Обратите внимание, что объектом вырезания является функция ГОД, которая отделяет от даты значение года.
- Вторая часть формулы более сложна. Необходимо посчитать количество дней, которые прошли от начала года в исходной дате. Для этой цели необходим вычитать дату последнего дня предыдущего года от заданной даты.
=ПРАВСИМВ(ГОД(A2);2)
A2-ДАТА(ГОД(A2);1;0)
Обратите внимание, что в формуле используется функция ДАТА, которая позволяет сгенерировать дату из трех аргументов: года, месяца и дня.
Год может быть любым числом в диапазоне 1900 до 999999. Месяц и день могут быть любыми числами причем как положительными, так и отрицательными.
Например, формула ниже возвращает кодовое число для 1 декабря 2014 года:
=ДАТА(2014;12;1)
В формуле, использованной в данном примере, возвращающей юлианскую дату в третьем аргументе функции ДАТА так где должны указываться дни, указано число 0. Значит, что Excel должен определить день, предшествующий первому дню первого месяца. Например, написанная ниже формула в пустой ячейке отобразить дату 31 декабря 1959 года:
=ДАТА(1960;1;0)
Значения, возвращаемые двумя формулами описанных выше и объединены с помощью оператора амперсант «&» предоставляет возможность преобразовать стандартную в юлианскую дату, которая состоит всего лишь из двух чисел: год и порядковый номер дня считая от начала этого же года.
Альтернативные формулы перевода дат в юлианский формат в Excel
Создадим альтернативную формулу для преобразования дат в юлианский формат JD:
Как видно на рисунке вторую часть формулы мы просто заменили на функцию РАЗНДАТ с указанным кодом в третьем аргументе «YD». Благодаря ему функция возвращает порядковый номер дня в неполном году исходной даты без учета полных лет.
Еще более простой способ воспользоваться функцией ТЕКСТ:
Это самая короткая формула, но с небольшой хитростью. Рассчитана на привычку Excel воспринимать и преобразовывать некоторый текст и числа в дату. Следовало бы добавить функцию ДАТАЗНАЧ для преобразования текстовой записи в дату. Поэтому более корректный вариант формулы будет немного длиннее и выглядеть так:
В Excel всегда можно найти несколько вариантов для решения той или иной задачи. Данный универсальный аналитический и вычислительный программный инструмент всегда предоставляет своим пользователям свободу выбора.
Обратный перевод юлианской даты в григорианскую
Для конвертирования тех же значений в обратном направлении будем использовать такую формулу:
В результате каждая дата преобразовалась в исходный вид, григорианского формата – GD.
