Почему нет функции разндат в эксель

Главная / Excel / Почему нет функции разндат в эксель

13.03.2023

15 мин.

Содержание

РАЗНДАТ(.) / DATEDIF(.)

Вычисляет количество дней, месяцев или лет между двумя датами.

ЭТОГО НЕТ В ОФИЦИАЛЬНОЙ СПРАВКЕ EXCEL:

На самом деле самой функции РАЗДАТ нет в справке Excel. Кроме того, его нет и в мастере функций. И когда вы вводите первые несколько букв имени функции вручную, Excel также не будет показывать подсказку в раскрывающемся списке. Это объясняется довольно просто. Эта функция изначально не является функцией Excel. Он поддерживается только Excel для совместимости с другими системами электронных таблиц. В Excel эта функция пришла из электронной таблицы Lotus 1-2-3. НО эта функция работает во всех версиях Excel

СИНТАКСИС:

= СТАВКА(дата начала, дата окончания, код)

АРГУМЕНТЫ:

1 start_date - дата начала.
2 дата окончания - дата окончания.
3 код - текстовое значение, указывающее, в каких единицах необходимо вернуть разницу дат.

Список допустимых значений аргумента "КОД"

КОД		ВОЗВРАЩАЕМОЕ ЗНАЧЕНИЕ
"Г"	"Ю"	Количество полных лет в периоде.
"М"	"М"	Количество полных месяцев в периоде.
"Д"	"Д"	Количество дней в периоде.
"МД"	"МД"	Разница между днями даты начала и даты окончания. Месяцы и годы дат игнорируются.
"ГМ"	"ЮМ"	Разница между датой начала и датой окончания месяца. Дни и годы дат игнорируются.
"ГД"	"ЮД"	Разница между днями даты начала и даты окончания. Дата год игнорируется.

ОГРАНИЧЕНИЯ:

Если аргумент даты окончания предшествует аргументу даты начала, функция вернет значение ошибки #ЧИСЛО.
Если один из аргументов start_date или end_date не является допустимой датой, функция вернет значение ошибки #VALUE.

Расчет количества дней между датами в Эксель

Довольно часто при работе с датами в таблице Excel возникает необходимость определить интервал времени между ними, который измеряется в днях. В Excel есть разные способы выполнить эту задачу. Давайте посмотрим на них.

Расчет разности дат

Для корректной работы с датами необходимо предварительно задать правильный формат ячеек. В противном случае могут возникнуть проблемы с корректным отображением дат, что сделает невозможным дальнейшую обработку.

На самом деле Excel умеет самостоятельно определять формат ячейки после внесения в нее информации. Но этот механизм не всегда работает как надо, поэтому следует заранее самостоятельно настроить формат ячейки или проверить, правильно ли его определила программа.

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

по умолчанию мы должны быть на вкладке «Число» (если это не так, переключитесь на нее);
в списке «Числовые форматы» найдите позицию «Дата» и нажмите на нее;
в поле «Тип» выбрать нужный вариант представления данных, в соответствии с которым они будут отображаться на листе;
нажмите ОК.

Метод 1: использование формулы вычитания

Начнем, пожалуй, с самого простого способа — вычитания двух дат, представленных в виде обычной формулы.

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

ВАЖНО: Количество дней указывается одним целым числом, поэтому ячейка для вывода должна иметь формат, отличный от «Дата», а именно «Общий». В противном случае результат будет отображаться в формате даты, что не позволит вам оценить правильность расчетов. Чтобы определить текущий формат ячейки, достаточно посмотреть на соответствующее поле, расположенное в разделе инструментов «Число» на ленте вкладки «Главная“.

Формула для расчета количества дней между датами в Excel

в последней ячейке введите знак “=“;
нажмите на ячейку с датой окончания (позже);
введите с клавиатуры символ «–» (знак минус);
нажмите на ячейку с датой начала.

Метод 2: функция РАЗНДАТ

Следующий способ — определить разницу между двумя датами с помощью функции DIFFERENT.

Функция имеет следующий синтаксис: =RATEDATE(дата начала, дата окончания, единица измерения).

Если с первыми двумя аргументами все понятно - суть их соответствует названию, то на третьем ("единице") стоит остановиться подробнее, так как именно он позволяет варьировать результат использования функции, выбирая единица измерения разницы между датами:

«d» — количество дней между датами;
«m» — количество целых месяцев между датами;
«у» — количество целых лет между датами;
"ГМ" - разница между датами в месяцах;
"МД" - разница между датами в днях, без учета месяцев и лет;
«ГД» — это разница между датами в днях без учета лет.

В нашем случае мы покажем пример с использованием аргумента «YD», так как целью расчета является определение количества дней между датами в году.

Основное отличие от простого вычитания одной даты из другой заключается в порядке аргументов функции: в качестве первого аргумента выбирается первая (более ранняя дата), а в качестве второго - последняя (более поздняя) дата. Ниже опишем алгоритм расчета.

Выбираем целевую ячейку, проверяем формат по описанной выше методике.
Описанной функции нет в мастере функций, поэтому вводим ее в ячейку вручную, строго по синтаксису.
Нажмите Enter и проверьте результат. Как мы видим, он похож на тот, который мы получили, используя простую формулу вычитания. Так что все сделано правильно.

Метод 3: функция ЧИСТРАБДНИ для определения количества рабочих дней

Третий описанный способ позволяет рассчитать количество дней с дополнительным условием – исключением из него нерабочего дня (выходных и праздничных дней.

Этот метод использует функцию ЧИСТРАБДНИ со следующим синтаксисом: =ЧИСТРАБДНИ(дата начала,дата окончания,[праздники]).

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

Приступаем к расчетам:

Так как описываемая функция присутствует в списке мастера функций, воспользуемся ею для большей наглядности: становимся на ячейку, где хотим отобразить результат и нажимаем кнопку «Вставить функцию» (fx) слева от поля панель формул.
В открывшемся окне мастера функций:
- выбрать категорию «Полный алфавитный список”;
- найти и нажать в списке операторов на строку «ЧИСТРАБДНИ”;
- нажмите ОК.
Перед нами появляется окно аргумента функции. Здесь вы можете ввести адреса ячеек вручную или, находясь в поле для заполнения значений аргументов, нажать на нужную ячейку на листе. После выбора даты начала и окончания, а также указанного праздника (адреса ячейки) нажмите ОК.
Предположим, на этот раз мы заранее не установили формат последней ячейки как «Общий». Затем мы получим результат, отображаемый в виде даты.
Поэтому необходимо еще раз проверить формат по описанному выше алгоритму и изменить его на «Общий”.

Заключение

С помощью описанных выше способов довольно легко посчитать количество дней между двумя датами как с дополнительными критериями, так и без них. Формула вычитания и функция РАЗДАТ отлично подходят для простого подсчета дней, а функция ЧИСТРАБДНИ поможет вычислить количество рабочих дней между датами. Окончательный выбор зависит от поставленной перед пользователем задачи.

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

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

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

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

РАЗДАТ(дата начала; дата окончания; метод измерения)

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

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

Важность

Описание

разница в целый месяц

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

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

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

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

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

В файле примера значение аргумента даты начала помещается в ячейку A2, а значение аргумента даты окончания — в ячейку B2 .

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

Формула =RADIDAT(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, дата окончания 03.01.2008 Результат: 2 (дни), так как 2008 год високосный

Эту формулу можно заменить простым выражением =INT(B2)-INT(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 (см раздел Расчет возраста) есть кривая формула для расчета количества месяцев между двумя датами:

Если вместо функции TDATE() - текущая дата использовать дату 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 и измененная дата начала 02.01. 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) В ячейке C7 должна быть разница целых месяцев (см пункт 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, дата окончания 03.01.2009 Результат3: 4 (дня) - совершенно непонятный и НЕПРАВИЛЬНЫЙ результат. Ответ должен быть =1. Также результат расчета зависит от версии EXCEL.

Версия EXCEL 2007 с пакетом обновления 3 (SP3:

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

EXCEL 2007 версия:

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

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

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

Эта формула является просто эквивалентным (в большинстве случаев) выражением RAZDAT() с параметром 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 дней. Это реально?

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

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

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