Вычитание дат в Excel: быстрые формулы и практические советы

TL;DR

Excel хранит даты как серийные числа. Для вычитания можно использовать простые арифметические операции, функцию EDATE для месяцев, DATE + YEAR/MONTH/DAY для лет и комбинированных изменений, а также DATEDIF или DAYS/NETWORKDAYS для получения разницы в днях. Проверьте формат ячеек и учтите особенности конца месяца и високосных годов.

Введение

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

Перед началом отформатируйте ячейки: нажмите ⌘+1 на macOS или CTRL+1 на Windows и выберите нужные формат «Дата» или «Число» в зависимости от задачи.

Как вычитать даты: общая идея

Excel хранит дату как целое число (дни от «нуля») и дробную часть для времени. Поэтому:

• Вычитание двух дат даёт число дней между ними (целое число).
• При прибавлении или вычитании дней можно просто складывать/вычитать числа.
• Для месяцев и лет используют функции, которые учитывают длину месяца и високосные годы.

Ниже — практические приёмы с примерами и пояснениями.

1. Вычитание месяцев: функция EDATE

Функция EDATE(start_date, months) сдвигает дату на указанное количество месяцев. Для вычитания используйте отрицательное значение months.

Пример шага за шагом:

1. Введите исходные даты в столбец A (например, A2 = 2024-03-31).
2. В столбце B укажите количество месяцев для вычитания как отрицательное число (например, B2 = -1).
3. В ячейку C2 вставьте формулу: =EDATE(A2,B2) и скопируйте вниз.

Примечание: если целевой месяц короче (например, переход с 31 марта на февраль), EDATE вернёт последний день целевого месяца (високосный год учитывается — для 2024 года это 29 февраля).

Важно: EDATE — удобна для корректного перехода между месяцами, когда простой арифметики с днями недостаточно.

2. Вычитание дней: простая арифметика и функции

Проще всего вычитать дни, складывая дату с отрицательным числом дней.

Пример:

1. Исходные даты в столбце A.
2. Количество дней для вычитания в столбце B как отрицательное число (например, -7).
3. В C2: =A2+B2 — это и есть новая дата.

Чтобы получить число дней между двумя датами (например, B2 − A2) используйте =B2-A2 или функцию DAYS(B2,A2).

Если нужно учитывать только рабочие дни, используйте NETWORKDAYS(start_date,end_date,holidays) — она исключит выходные и опционально праздничные дни.

Совет: если вы уменьшаете дату на несколько дней и хотите визуально отметить изменение, добавьте флажок/галочку в отдельный столбец.

3. Вычитание лет: сочетание DATE и YEAR/MONTH/DAY

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

Пример:

1. Исходные даты в A2.
2. Количество лет для вычитания в B2 (например, -3).
3. В C2: =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)).

Это изменит только год, оставив месяц и день прежними. Учтите ситуацию 29 февраля: при переходе с 29 февраля на невисокосный год DATE вернёт 1 марта соответствующего года или последний валидный день — поведение зависит от комбинации YEAR/MONTH/DAY, поэтому проверьте такие случаи в своих данных.

4. Одновременное вычитание лет, месяцев и дней

Если нужно менять все три компонента сразу, используйте формулу, объединяющую год/месяц/день:

1. В A2 — исходная дата.
2. В B, C, D — соответственно изменения для лет, месяцев и дней (отрицательные для вычитания).
3. В E2: =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2).

Excel корректно нормализует превышение значений месяцев/дней (например, MONTH(A2)+C2 может выйти за пределы 1–12, и DATE приведёт это в корректную дату следующего/предыдущего года).

Важно: все значения будут затронуты отрицательными числами, поэтому проверяйте крайние случаи (конец месяца, 29 февраля).

Дополнительные способы и полезные функции

• DATEDIF(start,end,unit) — возвращает разницу в годах/месяцах/днях: единицы "d", "m", "y", "ym", "md", "yd". Эта функция присутствует в Excel, но документирована плохо.
• DAYS(end,start) — простая разница в днях.
• NETWORKDAYS(start,end,holidays) — рабочие дни.
• Для сложных обработок даты можно преобразовать в Power Query, Office Scripts, VBA или внешние скрипты (Python/pandas).

Когда методы дают неверный результат: типичные ошибки и ограничения

• Формат ячеек: если ячейка отформатирована как текст, формула не сработает.
• Временная часть: если в ячейках есть время, B2-A2 даст дробное значение; округляйте или используйте INT при необходимости.
• Конец месяца: при переходах между месяцами с разным числом дней EDATE и DATE ведут себя по-разному — проверяйте ожидаемое поведение.
• Система дат Excel: Windows по умолчанию использует систему с датой-началом 1900 (включая известную особенность с 1900 годом), Mac может использовать 1904-систему. Это важно при обмене файлами между платформами.
• Праздники и выходные: простое вычитание не учитывает их — используйте NETWORKDAYS.

Альтернативные подходы

• Power Query: удобно трансформировать большие таблицы с датами и применять сдвиги массово.
• VBA/Office Scripts: автоматизация сложных правил или валидация «на лету».
• Google Sheets: большинство формул совместимы, но синтаксис и поведение некоторых функций может отличаться.
• Внешние инструменты: Python (pandas) — для больших объёмов и сложной логики.

Чит‑шит: быстрые формулы (шпаргалка)

• =A2+B2 — прибавить/вычесть дни (B2 может быть отрицательным).
• =EDATE(A2,-3) — вычесть 3 месяца.
• =DATE(YEAR(A2)-2,MONTH(A2),DAY(A2)) — вычесть 2 года.
• =DATE(YEAR(A2)+B2,MONTH(A2)+C2,DAY(A2)+D2) — комбинированное изменение (B2/C2/D2 могут быть отрицательными).
• =DAYS(B2,A2) — число дней между A2 и B2.
• =DATEDIF(A2,B2,"y") — целое число полных лет между датами.
• =NETWORKDAYS(A2,B2,holidays_range) — рабочие дни между датами.

Критерии приёмки

Проверьте каждую формулу на следующих тестах:

1. Исходная дата: 2024-03-31, вычесть 1 месяц → ожидается 2024-02-29 (високосный год).
2. Исходная дата: 2023-03-31, вычесть 1 месяц → ожидается 2023-02-28.
3. Исходная дата: 2020-02-29, вычесть 1 год → ожидается 2019-02-28 или 2019-03-01 в зависимости от логики — зафиксируйте требуемое поведение и проверьте формулы.
4. Разница между 2024-01-01 и 2024-01-31 с NETWORKDAYS и без — сравните значения с учётом выходных.

Если формулы дают ожидаемые результаты в этих случаях, можно считать задачу выполненной.

Чеклисты по ролям

Аналитик:

• Убедиться в корректности формата дат.
• Протестировать крайние случаи (конец месяца, високосные годы).
• Добавить примеры в документацию.

Бухгалтер:

• Использовать NETWORKDAYS для расчёта рабочих дней и оплат.
• Проверить праздничные дни в отдельном списке.

Руководитель проекта:

• Автоматизировать проверки дат для планирования сроков.
• Уточнить требуемое поведение при переносе дат (например, «последний рабочий день месяца»).

Короткий словарь (1 строка каждому)

• EDATE — сдвиг даты на заданное число месяцев.
• DATE — строит дату из года, месяца и дня.
• YEAR/MONTH/DAY — функции для извлечения компонентов даты.
• DATEDIF — возвращает разницу между датами в годах/месяцах/днях.
• NETWORKDAYS — считает рабочие дни между датами.

Итог и рекомендации

Excel предлагает несколько безопасных способов вычитать даты: простое арифметическое вычитание для дней, EDATE для месяцев и комбинацию DATE(YEAR+... ) для лет и сложных сдвигов. Всегда проверяйте формат ячеек и крайние случаи (конец месяца, високосные годы). Для массовой обработки больших наборов данных рассмотрите Power Query или скрипты.

Важно: прежде чем применить формулы в рабочей книге с критичными расчётами, прогоните контрольные тесты (см. раздел «Критерии приёмки»).

Заметка: если вы работаете с международными файлами, проверьте системную основу дат (1900 vs 1904) и локаль Excel при обмене файлами между Windows и macOS.

Краткое резюме и следующие шаги:

• Проверьте формат ячеек (⌘+1/CTRL+1).
• Выберите подходящую формулу: A2+B2 для дней, EDATE для месяцев, DATE(...) для лет и комбинированных изменений.
• Пропишите тесты для крайних случаев и автоматизируйте проверку, если нужно работать с большим количеством данных.