Сколько лет, месяцев, дней прошло с конкретной даты в EXCEL

Определим, сколько лет, месяцев и дней прошло с определенной даты, а также решим обратную задачу.

Для вычислений длительностей интервалов дат удобней всего использовать недокументированную функцию РАЗНДАТ() . Этой функции нет в справке EXCEL2007 и в Мастере функций ( SHIFT+F3 ), но она работает ( с некоторыми огрехами ).

Если в ячейке В2 содержится сегодняшняя дата, а в А2 –дата начала отсчета (например, день рождения), то формула: =РАЗНДАТ(A2;B2;"y")&" г. "& РАЗНДАТ(A2;B2;"ym")&" мес. "& РАЗНДАТ(A2;B2;"md")&" дн."

рассчитает сколько лет, месяцев, дней прошло с конкретной даты и вернет результат в виде 37 г. 5 мес. 1 дн .

Как показано в статье о функции РАЗНДАТ() , лучше избегать применения этой функции с аргументом "md", т.к. она может выдать неправильный результат. Поэтому часть формулы РАЗНДАТ(A2;B2;"md") лучше заменить ее эквивалентом: =ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);ДЕНЬ(B2)-ДЕНЬ(A2))

Итоговая формула приведена в файле примера:

=РАЗНДАТ(A2;B2;"y")&" г. "&РАЗНДАТ(A2;B2;"ym")&" мес. "&ЕСЛИ(ДЕНЬ(A2)>ДЕНЬ(B2);ДЕНЬ(КОНМЕСЯЦА(ДАТАМЕС(B2;-1);0))-ДЕНЬ(A2)+ДЕНЬ(B2);ДЕНЬ(B2)-ДЕНЬ(A2))&" дн."

Уточним склонения: год/ лет, месяца/ месяцев, дня/ дней

В случае, если получается целое количество лет, то формула вернет результат в виде 37 г. 0 мес. 0 дн. , что не очень красиво.

Воспользовавшись идеями, подсказанными нашим пользователем МСН, также просклоняем месяцев/ месяца и дней/ дня

=ЕСЛИ(РАЗНДАТ(A2;B2;"y");РАЗНДАТ(A2;B2;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(A2;B2;"y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(A2;B2;"ym");РАЗНДАТ(A2;B2;"ym")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(A2;B2;"ym")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(A2;B2;"md");РАЗНДАТ(A2;B2;"md")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(A2;B2;"md")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)

Окончательно результат будет выглядеть так: 37 лет 1 месяц 4 дня .

О вычислении страхового стажа

Формулы этой статьи НЕ позволяют правильно вычислить страховой (трудовой) стаж с точностью до дней. Поясним сказанное на примере (также см. статью Функция РАЗНДАТ() - Вычисление разности двух дат в днях, месяцах, годах в MS EXCEL )

Найдем разницу дат 30.01.15. и 16.03.2015. Функция РАЗНДАТ() с параметрами md и ym подсчитает, что разница составляет 1 месяц и 14 дней.

Алгоритм расчета этой функции следующий: сначала функция последовательно прибавляет месяцы к дате начала, до тех пор пока получившаяся дата меньше конечной. Т.е. к 30.01.2015 функция прибавит 1 месяц и получит 30.02.2015, но такой даты не существует, в феврале 2015г. 28 дней! Ничего страшного, функция запоминает, что нужно отнять 2 дня, чтобы получить правильную дату! Затем идет подсчет оставшихся дней в марте - их 16. Затем, функция к 16 дням прибавляет -2, и получаем 14 дней. Естественно, такой расчет не верен.

Получается, что функцию РАЗНДАТ() можно использовать только для расчета полных лет и месяцев , но без дней. Рассмотренные выше формулы будут в основном возвращать правильный результат (иногда, конечно, будет возникать погрешность в 1-2 дня в меньшую сторону), но часто удобство применения этой функции перевешивает требования к точности. Такой подход безусловно не годится для расчета страхового стажа для определения размера пособий по временной нетрудоспособности (об этом см. статью Расчет страхового (трудового) стажа в MS EXCEL ) .

Обратная задача

Теперь решим обратную задачу: задав количество количество лет, месяцев и дней, рассчитаем конечную дату.

Конечную дату будем рассчитывать двумя способами: через функцию ДАТА() и ДАТАМЕС()

= ДАТА(ГОД(A8)+B8;МЕСЯЦ(A8)+C8;ДЕНЬ(A8)+D8)

=ДАТАМЕС(ДАТА(ГОД(A8)+B8;МЕСЯЦ(A8);ДЕНЬ(A8));C8)+D8

Вычислив конечную дату, затем вычислим с помощью РАЗНДАТ() разницу между ними (должна совпасть с заданной). И наконец, из конечной даты получим начальную, вычтя заданное количество лет, месяцев и дней с использованием формул:

= ДАТА(ГОД(E8)-B8;МЕСЯЦ(E8)-C8;ДЕНЬ(E8)-D8)

=ДАТАМЕС(ДАТА(ГОД(E16)-B8;МЕСЯЦ(E16);ДЕНЬ(E16));-C8)-D8

Если для начальной даты 15.01.2015 (строка 8 в файле примера, лист Обратная задача ) все вычисления понятны и одинаковы (добавили 1 год, 2 месяца и 5 дней), получили 20.03.2015, разница составила теже 1 год, 2 месяца и 5 дней, расчетная начальная дата совпала с заданной начальной датой, то для 29.01.2015 все не так гладко (см. строку 9). Для наглядности прибавим 1 месяц.

Как мы уже знаем из статьи Функция ДАТАМЕС() в MS EXCEL функции ДАТА() и ДАТАМЕС() не эквивалентны при прибавлении месяцев:

При прибавлении месяцев между ДАТАМЕС() и ДАТА() существует разница. Прибавим к 29.01.2015 один месяц:

• =ДАТАМЕС("29.01.2015";1) вернет 28.02.2015, т.к. 30 февраля не существует, то функция вернет последний день месяца, т.е. 28.02.2014 ("лишние" 2 дня будут отброшены);
• =ДАТА(ГОД("29.01.2015");МЕСЯЦ("29.01.2015")+1;ДЕНЬ("29.01.2015")) вернет 02.03.2015: "лишние" 2 дня (29 и 30 февраля) будут прибавлены к дате.

Расчет с помощью ДАТАМЕС() кажется логичней, хотя функция РАЗНДАТ() с этим не согласна: разницу, совпадающей с заданной она возвращает только для варианта с функцией ДАТА() .

Расчетная начальная дата в обоих случаях не совпадает с заданной начальной датой: получим 01.02.2015 и 28.01.2015 вместо 29.01.2015.

Как быть? Вероятно, решение будет зависеть от конкретной задачи, стоящей перед пользователем. Необходимо помнить, что нужно использовать ДАТАМЕС() при добавлении (вычитании) месяцев, а РАЗНДАТ() - это недокументированная и, соответственно, не гарантирующая правильность расчетов функция. Об этой функции написана статья Функция РАЗНДАТ() - Вычисление разности двух дат в днях, месяцах, годах в MS EXCEL , в которой дается формула, учитывающая вышеуказанную ситуацию (см. раздел "Еще раз о кривизне РАЗНДАТ() ").