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

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

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

Синтаксис

=DATEDIF(дата начала,дата окончания,единица измерения) – английская версия

= РАЗДАТ (дата начала, дата окончания, единица измерения) — русская версия

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

• дата начала (start date): дата, с которой начинается расчет разницы во времени между двумя датами. Дата может быть указана в виде текста в кавычках, числового значения или результата вычисления (например, в функции DATE());
• дата окончания (end date): дата, на которую заканчивается расчет разницы во времени между двумя датами. Он также может быть указан как текст в кавычках, числовое значение или результат вычисления;
• unit (единица измерения): Этот аргумент определяет, в каком измерении должна отображаться разница между двумя датами (год, месяц, день). Существует 6 различных типов отражения данных:

• «Y» — возвращает данные в количестве лет между двумя датами;
• «М» — возвращает данные в количестве месяцев между двумя датами;
• «D» — возвращает данные в количестве дней между двумя данными;
• «МД» — возвращает данные в количестве дней между двумя датами, но не учитывает целые годы, месяцы, которые уже прошли при подсчете данных. Например, если вычислить количество дней между двумя датами: 1 января 2017 г и 3 марта 2017 г., функция вернет значение «2», поскольку не учитывает два последних полных месяца (январь, февраль);
• «ГМ» — возвращает данные в количестве месяцев между двумя датами, но не учитывает все годы, уже прошедшие между двумя вычисляемыми датами;
• «ГД» — возвращает данные в количестве дней между двумя датами, но не учитывает полные годы, которые уже прошли между двумя вычисляемыми датами.

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

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

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

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

Читайте также: Как перенести строку в ячейку Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=РАЗДАТ(A2;B2;»м”)

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

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

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

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

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

Тут ситуация немного сложнее. Суббота, воскресенье и праздничные дни не учитываются. Для такого расчета лучше использовать функцию NETWORK DAYS из категории Дата и время. В качестве аргументов этой функции необходимо указать даты начала и окончания, а также ячейки с датами выходных (отпусков, больничных, отпусков, выходных и т д):

Примечание. Эта функция появилась в стандартном наборе функций Excel, начиная с версии 2007 года. В более старых версиях необходимо сначала включить надстройку Analysis Pack. Для этого заходим в меню Инструменты — Дополнения (Tools — Add-ons) и ставим галочку напротив пакета анализа (Analisys Toolpak). После этого в Мастере функций на вкладке «Дата и время» появится нужная нам функция «ЧИСТЫЕ ДНИ).

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

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

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

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

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

Разве ты не родился в понедельник? Нет? Безопасный? Можно легко проверить с помощью функции ДЕНЬНЕД на вкладке Дата и время.

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

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

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

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

Как прибавить (вычесть) несколько месяцев к дате в Excel

Чтобы добавить (или вычесть) определенное количество месяцев к дате, используйте функцию ДАТА или ДАТА (EDATE), как показано ниже.

Пример 1. Прибавляем несколько месяцев к дате с помощью функции ДАТА

Например, если список дат находится в столбце A, введите количество месяцев, которое вы хотите добавить (положительное число) или вычесть (отрицательное число) в ячейке, скажем, C2.

Введите следующую формулу в ячейку B2, щелкните выделенный угол ячейки и перетащите его вниз по столбцу B с помощью мыши к последней заполненной ячейке в столбце A. Формула из ячейки B2 будет скопирована во все ячейки столбца B.

=ДАТА(ГОД(A2),МЕСЯЦ(A2)+2$C$,ДЕНЬ(A2))
=ДАТА(ГОД(A2),МЕСЯЦ(A2)+2$C$,ДЕНЬ(A2))

Давайте посмотрим, что делает эта формула. Логика в формуле ясна и очевидна. Функция ДАТА(год, месяц, день) принимает следующие аргументы:

• год с даты в ячейке A2;
• Месяц с даты в ячейке A2 + количество месяцев в ячейке C2;
• День с даты в ячейке A2;

Все просто! Если вы введете отрицательное число в C2, формула будет вычитать месяцы, а не добавлять их.

Естественно, ничто не мешает вам ввести минус прямо в формулу вычитания месяцев:

=ДАТА(ГОД(A2),МЕСЯЦ(A2)-$C$2,ДЕНЬ(A2))
=ДАТА(ГОД(A2),МЕСЯЦ(A2)-$C$2,ДЕНЬ(A2))

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

• Добавьте месяцы к дате:=ДАТА(ГОД(A2),МЕСЯЦ(A2)+2,ДЕНЬ(A2))
  =ДАТА(ГОД(A2),МЕСЯЦ(A2)+2,ДЕНЬ(A2))
• Вычесть месяцы из даты:=ДАТА(ГОД(A2),МЕСЯЦ(A2)-2,ДЕНЬ(A2))
  =ДАТА(ГОД(A2),МЕСЯЦ(A2)-2,ДЕНЬ(A2))

Пример 2. Прибавляем или вычитаем месяцы из даты с помощью функции ДАТАМЕС

В Excel есть специальная функция, которая возвращает дату на определенное количество месяцев назад или раньше заданной даты — это функция ДАТАМЕС. Он доступен в последних версиях Excel 2007, 2010, 2013 и новом Excel 2016.

Когда вы используете EDATE, вы указываете следующие два аргумента:

• Дата начала — дата, от которой отсчитывается количество месяцев.
• Месяцы — это количество месяцев, которое нужно добавить (положительное число) или вычесть (отрицательное число).

Эти формулы дадут тот же результат, что и формулы с функцией ДАТА в предыдущем примере:

При использовании функции ДАТА (EDATE) начальную дату и количество месяцев можно указать непосредственно в формуле. Даты можно указать с помощью функции ДАТА или в результате других формул. Например:

• Эта формула добавляет 10 месяцев к 7 мая 2015 года=ДАТА(ДАТА(2015;5;7);10)
  =ЭДАТА(ДАТА(2015;5;7);10)
• Эта формула вычитает 10 месяцев из сегодняшней даты=ДАТАВРЕМЯ(СЕГОДНЯ();-10)
  =ДАТЕР(СЕГОДНЯ(),-10)

Примечание. Функция ДАТАМЕС возвращает только целое число. Чтобы представить его как дату, вам нужно применить к ячейке формат даты. Как это сделать указано в статье Как изменить формат даты в Excel.

Как складывать и вычитать время в Excel

В Microsoft Excel вы можете добавлять и вычитать время с помощью функции ВРЕМЯ. Она позволяет обрабатывать единицы времени (часы, минуты и секунды) так же, как вы обрабатываете годы, месяцы и дни в функции DATE).

• Добавьте время в Excel:= A2 + ВРЕМЯ (часы; минуты; секунды)
  = A2 + ВРЕМЯ (часы, минуты, секунды)
• Вычесть время в Excel:= A2 — ВРЕМЯ(часы;минуты;секунды)
  = A2 — ВРЕМЯ (часы, минуты, секунды)

  где A2 — ячейка со временем изменения.

Например, чтобы добавить 2 часа 30 минут и 15 секунд ко времени в ячейке A2, используйте следующую формулу:

=A2+ВРЕМЯ(2;30;15)
=A2+ВРЕМЯ(2;30;15)

Чтобы добавить и вычесть единицы времени в одной формуле, просто добавьте знак минус «-» к нужным значениям. Следующая формула добавляет 2 часа 30 минут ко времени в ячейке A2 и вычитает 15 секунд:

=A2+ВРЕМЯ(2;30;-15)
=A2+ВРЕМЯ(2;30;-15)

Вы также можете ввести нужные значения в ячейки листа и ссылаться на них в формуле:

=A2+ВРЕМЯ($C$2,$D$2,$E$2)
=A2+ВРЕМЯ($C$2,$D$2,$E$2)

Если исходная ячейка содержит и дату, и время, предыдущая формула также работает нормально:

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

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

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

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

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

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

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

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

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

Преимущества использования мастера формул даты и времени

Помимо скорости и простоты, мастер даты и времени предлагает ряд других преимуществ:

• В отличие от обычной формулы РАЗДАТ, для формулы, созданной мастером, не имеет значения, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если дата 1 (дата начала) больше даты 2 (дата окончания).
• Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет выбирать между 11 различными комбинациями этих единиц.
• Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими электронными таблицами с другими, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в Excel.

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

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