Вычисление разности дат в Microsoft 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. Важно знать аргументы функции и как ими пользоваться;
• Данные даты в функции могут быть введены как:
  – дата указанная в виде текста в кавычках;
  – число;
  – как результат вычисления, формулы, функции.

2 популярных способа сравнения 2 дат в Excel

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

Первая дата больше второй, или нет

В этом случае нужно использовать аналогичную формулу, которая используется для проверки чисел: =A1>A2. Если эта проверка подтверждается, то в ячейку, куда была записана эта формула, возвращается значение “ИСТИНА”. Если же первое число меньше второго, то тогда будет записано “ЛОЖЬ”. Аналогично, только зеркально противоположно с формулой =A1

Функция ЕСЛИ, значение ИСТИНА или ЛОЖЬ

Это уже более сложный вариант, который дает возможность не только говорить, действительно ли одна дата больше другой, но и указывать, на какое количество дней. Чтобы это сделать, необходимо нажать по ячейке и записать туда формулу =ЕСЛИ(A2>B2;»Первая дата больше второй на»&» «&A2-B2&» «&»дней»;»Первая дата меньше второй на»&» «&B2-A2&» «&»дней»)

1

Здесь мы осуществляем проверку двух дат, и исходя из того, что у нас получилось в итоге, выводим результат, на сколько дней первая дата больше, чем вторая. Чтобы это сделать, нами был использовал оператор &, который объединяет две строки текста между собой и объединенный результат возвращает в ячейку. Далее получившийся текст можно использовать сам по себе или в других формулах.

Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах

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

Ее синтаксис элементарный.

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

Единственный из этих аргументов, который действительно может вызвать вопросы у неподготовленного читателя – это способ измерения. С его помощью мы задаем, в каких единицах будет измеряться разница между датами. Это может быть количество месяцев, лет или количество месяцев без учета лет. Еще один вариант – это количество дней без учета месяцев и лет. 

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

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

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

Разница дат в полных месяцах

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

То есть, формула следующая.

=РАЗНДАТ(A2;B2;”m”)

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно. 

Разница в днях без учета лет

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

1

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

Количество рабочих дней между двумя датами

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

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

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

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).  

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

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

Найдем разницу дат 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! Эта ошибка проявляется, когда в предыдущем месяце относительно конечной даты, дней меньше, чем дней начальной даты.

Формула разница дат в Excel

Формула возвращает разницу дат между сегодняшней и установленной планом в днях. Для решения данной задачи используется функция РАЗНДАТ в Excel: где найти эту формулу?

Данную функцию вы не найдете в мастере функций и даже на панели «ФОРМУЛЫ». Ее всегда нужно вводить вручную. Первым аргументом функции всегда должна быть новейшая дата, а вторым всегда – старшая дата. Третий аргумент функции определяет единицу измерения количества, которое возвращает функция =РАЗНДАТ(). В данном случае это символ «d» – дней. Это значит, что функция возвращает количество дней. Дальше следует оператор <7. То есть формула проверяет, если функция возвращает число меньше чем 7, то формула возвращает значение ИСТИНА и к текущей ячейке применяется условное форматирование. Ссылки на ячейки в первом аргумент абсолютная (значение неизменяемое), а во втором аргументе – относительная, так как проверятся будут несколько ячеек в столбце C.

При необходимости можно к данному диапазону добавить новое правило условного форматирования, которое будет предупреждать нас об окончании сроков за 2 недели.

Цвет форматирования для второго правила можно задать желтый. К одному и тому же диапазону должно быть применено 2 правила условного форматирования. Чтобы проверить выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами». Так как у нас сначала выполняется верхнее правило следует изменить их порядок в появившемся окне: «Диспетчер правил условного форматирования». Иначе все выделенные задачи будут иметь желтую заливку ячеек.

Полезный совет! Если к одному и тому же диапазону присвоено много правил – соблюдайте иерархию приоритетов порядка их выполнения в диспетчере управления правилами. Чем выше правило, тем выше его приоритет выполнения по отношению к другим расположенных ниже под ним.