Вычисления с датами до 01.01.1900 в EXCEL

Если возникла необходимость выполнить вычисления с датами до 01.01.1900, то придется прибегнуть к некоторым ухищрениям, чтобы использовать встроенные функции EXCEL для работы с датами.

Чтобы иметь возможность использовать встроенные функции EXCEL необходимо, чтобы даты были в диапазоне от 01.01.1900 до 31.12.9999. Но, иногда, этого диапазона дат недостаточно. Рассмотрим типичную задачу, в которой может возникнуть необходимость использования дат до 01.01.1900.

Задача

Определим сколько лет, месяцев и дней прожил Джордж Ноэл Гордон Байрон (дата рождения 22.01.1788, дата смерти 19.04.1824).

Решение

Для решения воспользуемся функцией РАЗНДАТ() , но перед этим прибавим к датам рождения и смерти по 2000 лет, чтобы иметь возможность использовать эту функцию, т.к на вход РАЗНДАТ() можно подавать даты от 01.01.1900 до 31.12.9999. (см. файл примера ).

Почему мы прибавили 2000 лет, а не, например, 1000? Это сделано для того чтобы учесть високосный ли год . Дело в том, что согласно действующего Григорианского календаря, год кратный 4 – високосный, но если год кратен 100, то он не считается високосным. А вот если он еще и кратен 400, то он все-таки високосный. Поэтому для обеспечения точности до дней лучше прибавлять к датам до 01.01.1900 величины кратные 400, т.е. 2000, а не 1000. В нашем случае, конечно, можно было бы прибавить и 400 лет, чтобы попасть в диапазон от 01.01.1900 до 31.12.9999.

Если в ячейку ввести значение 22.01.1788, то EXCEL воспримет его как текст. Как же прибавить к дате 22.01.1788, представляющей собой текстовое значение, 2000 лет? Для этого в файле примера день, месяц и год рождения/ смерти введены в отдельные ячейки. Затем к году необходимо прибавить 2000 лет и, наконец, перевести получившееся значение в формат даты с помощью функции ДАТА() : =ДАТА(B7;B5;B4) .

Для подсчета полного времени жизни с точностью до дня можно использовать формулу: =РАЗНДАТ(B8;C8;"y")&" лет " &РАЗНДАТ(B8;C8;"ym")&" мес. " &РАЗНДАТ(B8;C8;"md")&" дн."

Итак, Байрон прожил 36 лет 2 месяца и 28 дней.

ВНИМАНИЕ! Как показано в статье с описанием недокументированной функции РАЗНДАТ() , эта функция с параметром "md" может вернуть ошибочное значение (ошибка м.б. несколько дней в зависимости от версии EXCEL).

О том как EXCEL хранит дату можно прочитать в одноименной статье Как EXCEL хранит дату и время .