Функция РАЗНДАТ () — Вычисление разности двух дат в днях, месяцах, годах в EXCEL

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

Если Вам требуется рассчитать стаж (страховой) в годах, месяцах, днях, то, пожалуйста, воспользуйтесь возможностью расчетами выполненными в статье Расчет страхового (трудового) стажа в MS EXCEL.

Функции РАЗНДАТ () нет в справке EXCEL2007 и в Мастере функций ( SHIFT + F 3 ), но она работает, хотя и не без огрех.

Синтаксис функции:

РАЗНДАТ (начальная_дата; конечная_дата; способ_измерения)

Аргумент начальная_дата должна быть раньше аргумента конечная_дата .

Аргумент способ_измерения определяет, как и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот аргумент может принимать следующие значения:

Значение

Описание

«d»

разница в днях

«m»

разница в полных месяцах

«y»

разница в полных годах

«гм»

разница в полных месяцах без учета лет

«md»

разница в днях без учета месяцев и лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение, если день начальной даты больше дня конечной даты (например, в EXCEL 2007 при сравнении дат 28.02.2009 и 01.03.2009 результат будет 4 дня, а не 1 день). Избегайте использования функций с этим аргументом. Альтернативная формула приведена ниже.

«ярд»

разница в днях без учета лет ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение. Избегайте использования функций с этим аргументом.

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

В примере значения аргумента начальная_дата помещена в ячейку А2 , а значение аргумента конечная_дата — в ячейке В2 .

1. Разница в днях («d» )

Формула = РАЗНДАТ (A2; B2; «d») вернет простую разницу в днях между двумя датами.

Пример1: начальная_дата 25.02.2007, конечная_дата 26.02.2007 Результат: 1 (день).

Этот пример показыват, что при подсчете стажа необходимо использовать функцию РАЗНДАТ () с осторожностью. Очевидно, что если сотрудник работал 25 и 26 февраля, то отработал он 2 дня, а не 1. То же относится и к расчету полных месяцев (см. Ниже).

Пример2: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Результат: 28 (дней)

Пример3: начальная_дата 28.02.2008, конечная_дата 01.03.2008 Результат: 2 (дня), т.к. 2008 год — високосный

Эта формула может быть заменена общим выражением = ЦЕЛОЕ (B2) -ЦЕЛОЕ (A2). Функция ЦЕЛОЕ () округляет значение до меньшего целого и случая случая, если исходные даты введены вместе с временем суток (РАЗНДАТ () игнорирует время, т.е. дробную часть числа, см. Статью Как Excel хранит дату и время).

Примечание : Если интересуют только рабочие дни, то к оличество рабочих дней между двумя датами можно посчитать по формуле = ЧИСТРАБДНИ (B2; A2)

2. Разница в полных месяцах («m»)

Формула = РАЗНДАТ (A2; B2; «m») вернет количество полных месяцев между двумя датами.

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2007 Результат: 1 (месяц)

Пример2: начальная_дата 01.03.2007, конечная_дата 31.03.2007 Результат: 0

При расчете стажа, считается, что сотрудник отработавший все дни месяца — отработал 1 полный месяц. Функция РАЗНДАТ () так не считает!

Пример3: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 25 месяцев

Формула может быть заменена альтернативным выражением: = 12 * (ГОД (B2) -ГОД (A2)) — (МЕСЯЦ (A2) -МЕСЯЦ (B2 )) — (ДЕНЬ (B2)

Внимание : В справке MS EXCEL (см. раздел Вычисление возраста) имеется кривая формула для вычислений количества месяцев между двумя датами:

= (ГОД (ТДАТА ()) — ГОД (A3)) * 12 + МЕСЯЦ ( ТДАТА ()) — МЕСЯЦ (A3)

Если вместо функции ТДАТА () — текущая дата использовать дату 31.10.1961, а в А3 достижения 01.11.1962, то формула вернет 13, хотя фактически прошло 12 месяцев и 1 день (ноябрь и декабрь в 1961г. + 10 месяцев в 1962г.).

3. Разница в полных годах («y»)

Формула = РАЗНДАТ (A2; B2; «y») вернет количество полных лет между двумя датами.

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат: 2 (года)

Пример2: начальная_дата 01.04.2007, конечная_дата 01.03.2009 Результат: 1 (год)

Подробнее читайте в статье Полный возраст или стаж.

Формула может быть заменена альтернативным выражением: = ЕСЛИ (ДАТА (ГОД (B2); МЕСЯЦ (A2); ДЕНЬ (A2))

4. Разница в полных месяцах без учета лет («ym»)

Формула = РАЗНДАТ (A2; B2; «ym») вернет количество полных месяцев между двумя датами без учета лет (см. примеры ниже).

Пример1: начальная_дата 01.02.2007, конечная_дата 01.03.2009 Результат : 1 (месяц), т.к. сравниваются конечная дата 01.03.2009 и модифицированная начальная дата 01.02. 2009 (год начальная дата заменяется годом конечной даты, т.к. 01.02 меньше чем 01.03)

Пример2: начальная_дата 01.04.2007, конечная_дата 01.03. 2009 Результат: 11 (месяцев), т.к. сравниваются конечная дата 01.03.2009 и модифицированная начальная дата 01.04. 2008 (год начальная дата заменяется годом конечной дата за вычетом 1 года, т.к. 01.04 больше чем 01.03)

Вся эта вакханалия нужна, например, для подсчета сколько полных дней, месяцев и лет прошло с настоящего дня до сегодняшнего дня. Подробнее читайте в одноименной статье Сколько лет, месяцев, дней прошло с конкретной даты.

Формула может быть заменена альтернативным выражением: = ОСТАТ (C7; 12) В ячейке С7 должна содержаться разница в полных месяцах (см. п.2).

5. Разница в деньх без учета месяцев и лет («md»)

Формула = РАЗНДАТ (A2; B2; «md») вернет количество дней между двумя датами без учета месяцев и лет. Использовать функцию РАЗНДАТ () с этим аргументом не рекомендуется (см. Примеры ниже).

Пример1: начальная_дата 01.02.2007, конечная_дата 06.03.2009 Результат1: 5 (дней), т.к. сравниваются конечная дата 06.03.2009 и модифицированная начальная дата 01. 03 . 2009 (год и месяц дата даты заменяется годом и месяцем конечной даты, т.к. 01 меньше чем 06)

Пример2: начальная_дата 28.02.2007, конечная_дата 28.03.2009 Результат2: 0, т.к. сравниваются конечная дата 28.03.2009 и модифицированная начальная дата 28. 03 . 2009 (год и месяц начальная дата заменяется годом и месяцем конечной даты)

Пример3: начальная_дата 28.02.2009, конечная_дата 01.03.2009 Результат3: 4 (дня) — совершенно непонятный и НЕПРАВИЛЬНЫЙ результат. Ответ должен быть = 1. Более того, результат вычислений зависит от версии EXCEL.

Версия EXCEL 2007 с SP3:

Результат — 143 дня! Больше чем дней в месяце!

Версия EXCEL 2007:

Разница между 28.02.2009 и 01.03.2009 — 4 дня!

Причем в EXCEL 2003 с SP3 формула возвращает верный результат 1 день. Для значений 31.12.2009 и 01.02.2010 результат вообще отрицательный (-2 дня)!

Не советую использовать формулу с вышеуказанным значением аргумента. Формула может быть заменена альтернативным выражением: = ЕСЛИ (ДЕНЬ (A2)> ДЕНЬ (B2); ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B2; -1); 0)) — ДЕНЬ (A2) + ДЕНЬ (B2); ДЕНЬ (B2) -ДЕНЬ (A2))

Данная формула лишь эквивалетное (в большинстве случаев) выражение для РАЗНДАТ () с параметром md. О правильности этой формуле читайте в разделе «Еще раз о кривизне РАЗНДАТ ()» ниже.

6. Разница в деньх без учета лет («ярд»)

Формула = РАЗНДАТ (A2; B2; «ярд») вернет количество дней между двумя датами без учета лет. Использовать ее не рекомендуется по причинам.

Результат, возвращаемый формулой = РАЗНДАТ (A2; B2; «ярд») зависит от версии EXCEL.

Формула может быть заменена альтернативным выражением: = ЕСЛИ (ДАТА (ГОД (B2 ); МЕСЯЦ (A2); ДЕНЬ (A2))> B2; B2-ДАТА (ГОД (B2) -1; МЕСЯЦ (A2); ДЕНЬ (A2)); B2-ДАТА (ГОД (B2); МЕСЯЦ (A2) ; ДЕНЬ (A2)))

Еще раз о кривизне РАЗНДАТ ()

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

Имея формулу, эквивалентную РАЗНДАТ (), можно понять ход вычисления. Очевидно, что в нашем случае количество полных месяцев между датами = 1, т.е. весь февраль. Для дней вычислений, функция находит количество дней в предыдущем месяце относительно конечной даты, т.е. 28 (конечная дата марту, предыдущий месяц — февраль, а в 2015г. В феврале было 28 дней). После этого отнимает день начала и прибавляет день конечной даты = ДЕНЬ (КОНМЕСЯЦА (ДАТАМЕС (B6; -1); 0)) — ДЕНЬ (A6) + ДЕНЬ (B6), т.е. 28-30 + 16 = 14. На наш взгляд, между датами все же 1 полный месяц и все дни марта, т.е 16 дней, а не 14! Эта ошибка проявляется, когда в предыдущем месяце относительно конечной даты, дней меньше, чем дни начальной даты. Как выйти из этой ситуации?

Модифицируем формулу для расчета дней без учета месяцев и лет:

= ЕСЛИ (ДЕНЬ (A18)> ДЕНЬ (B18); ЕСЛИ ((ДЕНЬ (КОНМЕСЯЦА ( ДАТАМЕС (B18; -1); 0)) — ДЕНЬ (A18))

При применении новой функции необходимо учитывать, что разница в днях будет одинаковой для нескольких начальных дат (см. рисунок выше, дата 28-31.01.2015). пользователю решать в зависимости от условий задачи.

Оцените статью
Botgadget.ru
Добавить комментарий