Разница между датами в Excel: 5 формул с примерами

«Сколько дней прошло с даты заказа до отгрузки?», «Как посчитать стаж сотрудника?», «Сколько рабочих дней между стартом и дедлайном?» — всё это задачи про разницу между датами в Excel.

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

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

• очистить таблицу от мусора и странных символов — см. статью «10 способов очистить таблицу перед анализом»;
• проверить, что даты — это именно даты, а не текст (подробно в статье «Как привести текст к числу в Excel»);
• убрать лишние пробелы и невидимые символы из текстовых столбцов — см. «Как удалить невидимые символы в Excel».

Как Excel хранит даты и почему разность даёт дни

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

Например:

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

Формула 1. Простая разность дат: =B2–A2

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

Пример. Есть дата начала и дата окончания задачи:

В ячейку C2 введите:

=B2-A2

Результат: 9 — между 1 и 10 марта прошло 9 полных дней.

Что важно учесть

• Обе ячейки должны быть в формате Дата, а не текст.
• Если дата окончания раньше даты начала, получится отрицательное число.
• Если в датах есть лишние пробелы или странные символы, предварительно очистите их (см. статью «Как убрать лишние пробелы в Excel»).

Формула 2. ДНИ: разница между датами «по-документации»

Функция ДНИ делает то же самое, что вычитание дат, но явно: возвращает количество дней между двумя датами.

Синтаксис:

=ДНИ(дата_конца; дата_начала)

С теми же данными:

Формула в C2:

=ДНИ(B2;A2)

Результат: 9.

Когда полезно использовать ДНИ

• Когда в формуле важно явно показать, что речь именно о разнице дат.
• Когда формулой будет пользоваться кто-то ещё, и запись ДНИ(дата_конца;дата_начала) читается понятнее, чем просто B2-A2.
• В конструкциях с условиями (например, вместе с формулой ЕСЛИ): «если больше N дней — подсветить».

Формула 3. РАЗНДАТ: годы, месяцы и дни между датами

Иногда нужны не просто «дни между датами», а, например:

• сколько полных лет человек работает в компании;
• сколько полных месяцев длится проект;
• сколько дней осталось «сверх полных месяцев».

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

Общий вид:

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

Где единица измерения может быть:

Пример: стаж в годах, месяцах и днях

Допустим, в колонке A — дата выхода сотрудника, в B — дата оценки:

Формулы:

=РАЗНДАТ(A2;B2;"y")   // C2: полные годы
=РАЗНДАТ(A2;B2;"ym")  // D2: оставшиеся месяцы сверх полных лет
=РАЗНДАТ(A2;B2;"md")  // E2: оставшиеся дни сверх полных месяцев

В результате можно собрать человекочитаемый стаж вроде «6 лет 0 месяцев 15 дней». При желании это объединяется в одну строку текстовыми функциями (см. статью «Как объединить текст в Excel»).

Формула 4. ЧИСТРАБДНИ.МЕЖД: только рабочие дни между датами

В задачах с дедлайнами важно не только, сколько дней между датами, но и сколько из них рабочих. Для этого в новых версиях Excel есть функция ЧИСТРАБДНИ.МЕЖД.

Базовый вид:

=ЧИСТРАБДНИ.МЕЖД(дата_начала; дата_конца; выходные; [праздники])

• выходные — код или строка, указывающая, какие дни считать выходными;
• [праздники] — диапазон с датами праздников, которые тоже исключаются из расчёта.

Пример: рабочие дни между стартом задачи и дедлайном

Формула без учёта праздников:

=ЧИСТРАБДНИ.МЕЖД(A2;B2;1)

Здесь код 1 означает, что выходные — это суббота и воскресенье. Функция вернёт количество полных рабочих дней в указанном интервале.

Если в компании используется нестандартный график, можно воспользоваться строкой выходных. Например, строка "0000011" (пять рабочих дней и два выходных) будет означать, что рабочими считаются понедельник–пятница.

В более старых версиях Excel можно использовать функцию ЧИСТРАБДНИ, если достаточно стандартных выходных.

Формула 5. Разница между датой и «сегодня»: СЕГОДНЯ + вычитание или РАЗНДАТ

Частый кейс — посчитать:

• сколько дней осталось до дедлайна;
• сколько дней прошло с даты события;
• текущий стаж или возраст.

Для этого удобно использовать функцию СЕГОДНЯ(), которая возвращает текущую дату.

Пример: дни до дедлайна

Формула в C2:

=B2-СЕГОДНЯ()

Положительное число — дедлайн ещё впереди, ноль — день дедлайна, отрицательное — просрочка. При необходимости можно добавить условное форматирование (см. статью «Как выделить совпадения и различия в таблице» и «Формула ЕСЛИ: 15 примеров»).

Пример: стаж с текущей даты

Можно комбинировать СЕГОДНЯ и РАЗНДАТ:

=РАЗНДАТ(A2;СЕГОДНЯ();"y")

Здесь A2 — дата приёма, результат — полные годы стажа на текущую дату. Аналогично можно считать месяцы и дни, меняя третий аргумент на "m", "d" и т.д.

Типичные ошибки при расчёте разницы между датами

1. Даты как текст

Визуально всё выглядит как дата, но Excel хранит значение как текст — формулы возвращают странные числа или вообще ошибку. Признаки:

• дата выровнена по левому краю;
• формула =ТИП(ячейка) возвращает 2 (текст), а не 1 (число).

Решение — привести текст к числу (см. статью «Как привести текст к числу в Excel»), либо воспользоваться мастером «Текст по столбцам» (подробно в материале «Как разделить текст в Excel»).

2. Перепутаны местами дата начала и конца

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

3. Смешение дат и времени

Если в одной ячейке дата+время, а в другой только дата, разность может быть, например, 4,75 дня. Формально это правильно, но может сбивать.

Решение — либо взять только дату (обнулить время), либо явно округлить результат до целых дней:

=ОКРУГЛВНИЗ(ДНИ(дата_конца;дата_начала);0)

4. Формулы поверх «грязных» данных

Любые расчёты между датами будут надёжнее, если таблица заранее приведена в порядок: удалены дубликаты, очищены лишние символы, нормализовано форматирование. Для этого удобно использовать мини-методичку «Как навести порядок в таблице Excel».

Небольшой чек-лист по разнице между датами