Как посчитать количество дней между датами в Excel

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

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

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

1. Сначала выберите целевой диапазон ячеек, удерживая нажатой левую кнопку мыши.
2. Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду «Форматировать ячейки». Аналогичного результата можно добиться с помощью комбинации клавиш Ctrl + 1.
   
3. Перед нами окно настройки формата ячейки:
   • по умолчанию мы должны быть во вкладке «Число» (если нет, переключитесь на нее);
   • в списке «Числовые форматы» найдите запись «Дата» и щелкните по ней;
   • в поле «Тип» выберите нужный шаблон представления данных, на основании которого они будут отображаться на листе;
   • нажмите ОК.
     
4. Теперь все значения, введенные в ячейки выделенной области, будут интерпретироваться программой как данные в формате «Дата“.

Расчет количества дней

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

Выбираем место листа, на котором вы планируете производить расчеты. Щелкните выделение правой кнопкой мыши. Контекстное меню активировано. В нем выбираем пункт «Формат ячейки…». Кроме того, вы можете ввести сочетание клавиш Ctrl + 1.

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

Способ 1: простое вычисление

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

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

Выберите ячейку, в которой будет отображаться результат. Он должен установить общий формат. Последнее условие очень важно, потому что, если эта ячейка содержит формат даты, результат будет иметь формат «дд.мм.гг» или другой, соответствующий этому формату, что является неверным результатом вычисления. Текущий формат ячейки или диапазона можно просмотреть, выделив его на вкладке «Главная». В блоке инструментов «Номер» есть поле, в котором отображается этот индикатор.

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

В ячейке, отформатированной для общего формата, введите знак «=». Щелкните ячейку, в которой находится последняя из двух (конечных) дат. Затем нажмите знак «-» на клавиатуре. Затем выберите ячейку, содержащую предыдущую (начальную) дату).

Способ 2: функция РАЗНДАТ

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

«Единица» — это формат, в котором результат будет отображаться в выбранной ячейке. Единица, в которой будет возвращена сумма, зависит от того, какой символ будет подставлен в этот параметр:

• «Y» — полные годы;
• «М» — полные месяцы;
• «Д» — дни;
• «YM» — разница в месяцах;
• «MD» — разница в днях (месяцы и годы не учитываются);
• «YD» — это разница в днях (годы не учитываются).

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

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

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

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

Способ 3: вычисление количеств рабочих дней

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

В этой функции основные аргументы такие же, как и для оператора РАЗДАТ: даты начала и окончания. Кроме того, есть необязательная тема «Праздники».

Его следует заменить датами государственных праздников, если таковые имеются, за охватываемый период. Функция вычисляет все дни указанного интервала, за исключением субботы, воскресенья и дней, добавленных пользователем в аргумент «Праздники».

Выберите ячейку, в которой будет находиться сумма расчета. Нажмите кнопку «Вставить функцию».

Мастер откроется. В категории «Полный алфавитный список» или «Дата и время» ищем элемент «СЕТЕВЫЕ ДНИ». Выберите его и нажмите кнопку «ОК».

Откроется окно аргументов функции. Введите дату начала и окончания периода в соответствующие поля, а также даты любых праздников. Нажмите кнопку «ОК».

После проделанных выше манипуляций в заранее выбранной ячейке отобразится количество рабочих дней за указанный период.

Как видите, программа Excel предоставляет своему пользователю довольно удобный инструментарий для расчета количества дней между двумя датами. В то же время, если вам нужно рассчитать только разницу в днях, то более оптимальным вариантом будет использование простой формулы вычитания, а не использование функции DIITDATA. Но если вам нужно, например, посчитать количество рабочих дней, то на помощь приходит функция ОЧИСТКА ДНЕЙ. То есть, как всегда, пользователь должен выбрать инструмент выполнения после постановки конкретной задачи.

Вычисление возраста в накопленных годах, месяцах и днях

Вы также можете рассчитать возраст или время обслуживания других пользователей. Результатом может быть «2 года», «4 месяца», «5 дней» «.

1. Используйте РАЗНДАТ для поиска общего числа лет.

В этом примере дата начала находится в ячейке D17, а дата окончания — в E17. В формуле «y» возвращает количество полных лет между двумя днями.

2 для поиска месяцев используйте РАЗНДАТ еще раз, указав «ГМ».

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

3. Используйте другую формулу для поиска дней.

Теперь вам нужно найти количество оставшихся дней. Это можно сделать, написав формулу другого типа, показанную выше. Эта формула вычитает первый день конца месяца (05.01.2016) из исходной даты окончания в ячейке E17 (05.06.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (05.06.2016), в результате получаем 5 дней.

Предупреждение: мы не рекомендуем использовать аргумент RAISED «MD», потому что он может дать неверные результаты.

4 необязательно: Объедините три формулы в одну.

Вы можете ввести все три вычисления в одну ячейку, как показано в этом примере. Использование амперсандов, цитат и текста. Это более длинная формула ввода, но, по крайней мере, она все в одной из них. Совет. Нажмите Alt + Enter, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, если вы не видите всю формулу, нажмите CTRL + SHIFT + U.

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

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

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

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

3. В нашем случае установлено значение «Дата». Чтобы изменить его, снова вызовите окно формата ячейки и используйте алгоритм, описанный выше, чтобы установить «Общий» числовой формат. Щелкните ОК, чтобы сохранить изменения.
   
4. Теперь можно переходить непосредственно к расчетам:
   • вставить знак «=» в последнюю ячейку “=“;
   • кликните по ячейке с датой окончания (далее);
   • введите с клавиатуры символ «-» (знак минус);
   • щелкните ячейку с датой начала.
     
5. Чтобы получить результат, нажмите клавишу Enter. В результате мы получаем целое число — количество дней между двумя датами.
   

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

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

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

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

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

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

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

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

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

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

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

Функция имеет следующий синтаксис: = ДАТА (начальная_дата; конечная_дата; единица).

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

• «D» — количество дней между датами;
• «M» — количество полных месяцев между датами;
• «Y» — количество полных лет между датами;
• «ГМ» — разница дат в месяцах;
• «MD» — разница между датами в днях, исключая месяцы и годы;
• «ГД» — это разница между датами в днях без учета лет.

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

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

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

Особенности использования функции РАЗНДАТ

• — start_date: дата, с которой будет производиться расчет;
• — end_date: дата, до которой будет производиться расчет;
• — unit: аргумент, указывающий сумму, при которой будет выполняться расчет.

Примечание. Аргументы start_date и end_date могут быть числовыми значениями или результатом некоторых вычислений, выполненных вне ячеек (например, функция DATE (), функция TODAY()).

Примечание. Аргумент может принимать одно из шести значений:

• — «Y»: в результате вычислений между начальными аргументами отобразится количество целых лет;
• — «M»: после расчетов между начальными аргументами будет отображаться количество целых месяцев;
• — «D»: в результате вычислений между начальными аргументами отобразится количество целых дней;
• — «MD»: расчет покажет количество дней между датами, исключая месяцы между датами. Например, при вычислении разницы между 1 сентября 2018 г и 21 ноября 2018 г. (что составляет 2 месяца и 20 дней) со значением «MD» будет возвращено значение 20, поскольку последние 2 полных месяца не учитываются;
• — «ГМ»: при расчете будет отображаться количество месяцев между датами, исключая годы между датами. Например, при вычислении разницы между 1 сентября 2010 г и 1 ноября 2018 г. (что составляет 8 лет и 2 месяца) со значением «YM» будет возвращено значение 2, поскольку последние 8 полных лет учитываться не будут;
• — «ГД»: в результате вычислений будет отображаться количество дней между датами, исключая годы между датами. Например, при вычислении разницы между 1 сентября 2010 г и 2 сентября 2018 г. (что составляет 8 лет и 1 день) со значением «YD» будет возвращено значение 1, поскольку последние 8 полных лет учитываться не будут.

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

Предупреждение: если значение start_date больше значения end_date, возникнет ошибка #NUM!

Как посчитать сроки окончания действия договора в Excel

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

С помощью функции РАЗДАТ определяем, сколько дней прошло с момента заключения трудового договора. В новом столбце для ячейки C2 используйте формулу: = DATADIT (B2; TODAY (); «d») и распространите ее на весь столбец ниже.

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

Сначала мы определяем количество дней в году по дате (365 или 366) по формуле IF (OSTAT (YEAR (B2); 4); 365; 366), затем вычитаем, чтобы узнать, сколько осталось -C2.

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

В этой формуле используется функция ЕСЛИ с несколькими условиями. Если до конца срока осталось более 10 дней, формула возвращает значение «Действителен» и, если менее 10, одно из условий:

1. Если меньше 10 дней, но больше 0, то значение «Готово».
2. Если меньше 0, срок его действия истек».

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

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

Вычисление разности двух дат

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

Предупреждение: если значение start_date больше значения end_date, возникнет ошибка #NUM!

Синтаксис

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

= DATEDATE (start_date; end_date; unit) — русская версия

Разница в неделях

В этом примере дата начала находится в ячейке D13, а даты окончания — в ячейке E13. «d» возвращает количество дней. Однако обратите внимание на / 7 в конце. В этом случае количество дней делится на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также должен быть отформатирован как число. Нажмите CTRL + 1. Затем нажмите Число> Десятичные дроби: 2.

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

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

Функция имеет следующий синтаксис: = ДАТА (начальная_дата; конечная_дата; единица).

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

• «D» — количество дней между датами;
• «M» — количество полных месяцев между датами;
• «Y» — количество полных лет между датами;
• «ГМ» — разница дат в месяцах;
• «MD» — разница между датами в днях, исключая месяцы и годы;
• «ГД» — это разница между датами в днях без учета лет.

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

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

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

Как точно посчитать сколько прошло времени: лет, месяцев и дней?

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

Используя уже известную формулу = РАЗДАТ (B2; СЕГОДНЯ (); «Y») для ячейки C2, мы получаем количество полных лет каждого сотрудника. Давайте расширим эту формулу на весь столбец ниже.

При изменении формулы, использованной выше, значение единицы измерения изменяется с «Y» на «YM». Окончательный вид формулы: = ДАТАРЕД (B2; СЕГОДНЯ (); «ГМ»). Следовательно, в качестве вывода мы получаем количество полных месяцев без учета последних полных лет. Мы распространяем формулу на весь столбец ниже.

В конце концов, нам понадобятся дни, которые не входят в месяц. Опять же, замените единицу измерения «YM» на «YD», и вы получите следующую формулу, где на выходе будет количество дней: = RAZDAT (B2; TODAY (); «YD»).

Это решение можно элегантно улучшить, используя функцию SPECIAL () и вызывая окно аргументов, делая функцию следующим образом: = CONCATENATE (C2; «годы»; D2; «месяцы»; E2; «день (дни)»)

Использование функции YEARFRAC (получить общее количество месяцев между двумя датами)

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

Функция YEARFRAC примет в качестве входных данных дату начала и дату окончания и даст вам количество лет, прошедших в течение этих двух дат.

В отличие от функции DATEDIF, функция YEARFRAC выдаст десятичные значения, если между двумя датами не прошел год.

Например, если моя дата начала — 1 января 2020 года, а моя дата окончания — 31 января 2020 года, результатом функции YEARFRAC будет 0,833. Как только вы получите значение года, вы можете получить значение месяца, умножив его на 12.

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

Ниже приведена формула, которая сделает это: = YEARFRAC (A2; B2) * 12

Это даст вам месяцы в десятичных разрядах.

Если вы просто хотите получить количество целых месяцев, вы можете заключить приведенную выше формулу в INT (как показано на скриншоте ниже): = INT (YEARFRAC (A2, B2) * 12)

Еще одно важное различие между функцией DATEDIF и функцией YEARFRAC заключается в том, что функция YEARFRAC будет рассматривать дату начала как часть месяца. Например, если дата начала — 1 января, а дата окончания — 31 января, приведенная выше формула будет равна 1.

Ниже приводится сравнение результатов DATEDIF и YEARFRAC.

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

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

1. Вводим две даты, между которыми мы будем определять разницу в днях, в две разные ячейки ранее отформатированного диапазона.
2. Теперь мы выбираем ячейку, в которой собираемся отобразить результат нашего расчета.
   ВАЖНО: количество дней указывается простым целым числом, поэтому ячейка для его отображения должна иметь формат, отличный от «Дата» или «Общий». В противном случае результат будет отображаться в формате даты, что не позволит нам оценить правильность расчетов. Чтобы определить текущий формат ячейки, просто посмотрите на соответствующее поле, расположенное в разделе инструмента «Число» на ленте на вкладке «Главная“.
3. В нашем случае установлено значение «Дата». Чтобы изменить его, снова вызовите окно формата ячейки и используйте алгоритм, описанный выше, чтобы установить «Общий» числовой формат. Щелкните ОК, чтобы сохранить изменения.
4. Теперь можно переходить непосредственно к расчетам:
   • вставить знак «=» в последнюю ячейку “=“;
   • кликните по ячейке с датой окончания (далее);
   • введите с клавиатуры символ «-» (знак минус);
   • щелкните ячейку с датой начала.
5. Чтобы получить результат, нажмите клавишу Enter. В результате мы получаем целое число — количество дней между двумя датами.

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

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

Источники

• https://MicroExcel.ru/raznost-dat/
• https://excelka.ru/voprosy/kak-v-excel-poschitat-kolichestvo-mesyatsev-mezhdu-datami.html
• https://web-shpargalka.ru/excel-raznica-mezhdu-datami-v-mesjacah.php
• https://excelhack.ru/funkciya-datedif-razndat-v-excel/
• https://excelprost.ru/drugoe/kak-v-excel-poschitat-kolichestvo-mesyatsev-mezhdu-datami.html
• https://support.microsoft.com/ru-ru/office/%D0%B2%D1%8B%D1%87%D0%B8%D1%81%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5-%D1%80%D0%B0%D0%B7%D0%BD%D0%BE%D1%81%D1%82%D0%B8-%D0%B4%D0%B2%D1%83%D1%85-%D0%B4%D0%B0%D1%82-8235e7c9-b430-44ca-9425-46100a162f38
• https://vip-excel.ru/rasschitajte-kolichestvo-mesyacev-mezhdu-dvumya-datami-v-excel-prostye-formuly/