Подсчёт дней между датами

Одна из самых распространённых задач при работе с датами — определить, сколько времени разделяет две даты: сколько дней осталось до крайнего срока, сколько лет человеку или какова продолжительность проекта.

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

На этом уроке Вы освоите и быстрый способ с вычитанием, и точный способ с DATEDIF.

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

Результат — количество дней между датами. Если в A2 указано 1 июня, а в B2 — 18 июня, формула =B2-A2 возвращает 17.

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

Распространённая неожиданность: после вычитания дат ячейка с ответом иногда показывает дату вместо числа, например 1/17/1900.

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

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

Обычное вычитание считает промежутки между днями, а не сами дни. С 1 по 18 июня вычитание даёт 17, но всего календарных дней на самом деле 18, если считать обе даты.

Если в Вашей ситуации нужно включить и начальный, и конечный день, прибавьте к результату 1.

Таким образом, формула =B2-A2+1 даёт количество дней с включением обеих границ. Определите, какой вариант нужен в Вашем отчёте.

Для подсчёта полных месяцев или лет обычное вычитание неудобно. Для этого предназначена функция DATEDIF. Она принимает три аргумента: начальную дату, конечную дату и код единицы в кавычках.

Код единицы определяет, что именно подсчитывается:

• "D" считает дни
• "M" считает полные месяцы
• "Y" считает полные годы

Обратите внимание: DATEDIF — скрытая устаревшая функция; она работает, но не отображается в списке автодополнения Excel.

Используйте единицу "M", чтобы посчитать полные месяцы между двумя датами. Неполные месяцы не учитываются.

Если в A2 указано 15 января, а в B2 — 10 апреля, формула =DATEDIF(A2, B2, "M") возвращает 2, поскольку прошло только два полных месяца: третий месяц ещё не завершился к 10-му числу.

Именно так обычно измеряют срок подписки или длительность договора.

Единица "Y" возвращает количество полных лет, что является правильным способом вычислить возраст.

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

Это позволяет избежать ошибок округления, которые возникают при делении разницы в днях на 365.

У DATEDIF есть дополнительные коды единиц для разбиения промежутка на части:

• "YM" = месяцы, оставшиеся после подсчёта полных лет
• "MD" = дни, оставшиеся после подсчёта полных месяцев
• "YD" = дни, оставшиеся после подсчёта полных лет

С их помощью можно представить продолжительность в виде 2 лет, 3 месяцев и 5 дней, объединив три вызова DATEDIF.

Обычное вычитание считает каждый календарный день, включая выходные. Чтобы посчитать только рабочие дни, используйте NETWORKDAYS.

Эта функция считает будние дни между двумя датами и позволяет указать список праздников, которые нужно пропустить. Поэтому формула =NETWORKDAYS(A2, B2) возвращает количество дней с понедельника по пятницу, а необязательный третий аргумент указывает диапазон с праздниками.

Это подходящий инструмент для оценки сроков доставки и сроков по SLA.

Предположим, что в A2 указана дата оплаты счёта, и Вы хотите показать, на сколько дней он просрочен относительно сегодняшней даты, но выводить 0, если срок оплаты ещё не наступил.

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

Эта единственная формула даёт понятное и корректное количество дней просрочки для отчёта по взысканию задолженности.

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

Формула =INT((B2-A2)/7) показывает, сколько полных недель укладывается между двумя датами. Промежуток в 17 дней превращается в 2 недели.

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

Вы изучили несколько способов измерять промежутки времени:

• =B2-A2 даёт разницу в днях (сначала указывайте более позднюю дату); добавьте 1, чтобы включить обе границы.
• Измените формат ячейки с результатом на Число, если она показывает дату.
• DATEDIF(start, end, "D"/"M"/"Y") считает полные дни, месяцы или годы; это удобно для вычисления возраста и срока.
• NETWORKDAYS считает рабочие дни и может пропускать праздники.
• Используйте MAX(0, ...), чтобы избежать отрицательного количества дней просрочки.

Вы завершили курс «Функции даты и времени».