Datediff excel не работает

Функция DATEDIF (РАЗНДАТ) в Excel. Как использовать?

Функция DATEDIF (РАЗНДАТ) в Excel используется для подсчета количества лет, месяцев или дней между двумя датами. Хороший пример – подсчет возраста.

Что возвращает функция

Число, обозначающее количество лет/месяцев/дней между двумя датами. Какие значения будут отображаться (год, месяц или дни) зависит от того, что будет указано вами в атрибутах функции.

Синтаксис

=DATEDIF(start_date,end_date,unit) – английская версия

= РАЗНДАТ( нач_дата ; кон_дата ; единица) – русская версия

Аргументы функции

• start_date (нач_дата): дата, с которой начинается вычисление разницы во времени между двумя датами. Дата может быть указана как текст в кавычках, числовое значение, или результат какого-либо вычисления (например в функции ДАТА());
• end_date (кон_дата): дата, на которой вычисление разницы во времени между двумя датами будет окончено. Она, также, может быть указана как текст в кавычках, числовое значение, или результат какого-либо вычисления;
• unit (единица): Этот аргумент определяет в каком измерении будет отображена разница между двумя датами (год, месяц, день). Существует 6 различных типов отражения данных:

• “Y” – возвращает данные в количестве лет между двумя датами;
• “M” – возвращает данные в количестве месяцев между двумя датами;
• “D” – возвращает данные в количестве дней между двумя данными;
• “MD” – возвращает данные в количестве дней между двумя датами, но не учитывает целые годы, месяца, которые уже прошли при расчете данных. Например, если вы рассчитываете количество дней между двумя датами: 1 Января 2017 и 3 Марта 2017, функция выдаст значение “2”, так как не будет учитывать два прошедших полных месяцев (Январь, Февраль);
• “YM” – возвращает данные в количестве месяцев между двумя датами, но не учитывает целые годы, которые уже прошли между двумя расчетными данными;
• “YD” – возвращает данные в количестве дней между двумя датами, но не учитывает целые годы, которые уже прошли между двумя расчетными датами.

Дополнительная информация

• При вводе функции в ячейку, она не отображается в быстром меню выбора функций. Тем не менее функция работает во всех версиях Excel. Важно знать аргументы функции и как ими пользоваться;
• Данные даты в функции могут быть введены как:
  – дата указанная в виде текста в кавычках;
  – число;
  – как результат вычисления, формулы, функции.

Примеры использования функции РАЗНДАТ в Excel

Пример №1. Подсчет количества лет между двумя датами

На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.

Пример №2. Подсчет количества месяцев между двумя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество полных месяцев между двумя датами – 19 июня 1989 года и 26 марта 2017 года.

Функция возвращает значение “333”, что является количеством полных месяцев между датами указанными выше.

Пример №3. Подсчет количества дней между двумя датами

На примере выше, функция DATEDIF (РАЗНДАТ) в Excel возвращает количество дней между двумя датами. Например, функция возвращает значение “10142” дня между двумя датами – 19 июня 1989 года и 26 марта 2017 года.

Если вы хотите посчитать количество дней между двумя датами, исключая количество лет, которые уже завершены, вам нужно использовать аргумент “YD” в качестве третьего (пример на рисунке ниже):

В приведенном выше примере он возвращает значение “280”, то есть число дней между днями, исключая 27 полных лет.

Если вы хотите посчитать количество дней между двумя датами, исключая количество полных лет и месяцев, используйте атрибут “MD” в качестве третьего аргумента (как показано на рис. ниже):

На примере выше функция возвращает значение “7” означающие количество дней между двумя днями “19” и “26” число, исключая значения лет и месяцев.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник

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

history 8 апреля 2013 г.

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

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

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

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

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

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

Значение

Описание

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

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

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

разница в днях без учета месяцев и лет ВНИМАНИЕ! Функция для некоторых версий 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 (см. раздел Вычисление возраста) имеется кривая формула для вычисления количества месяце между 2-мя датами:

Если вместо функции ТДАТА() — текущая дата использовать дату 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. Разница в днях без учета лет («yd»)

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

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

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

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

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

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

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

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

Источник