Формулы дат в Excel: стаж, сроки и рабочие дни

Главное, что нужно знать про даты в Excel

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

Сегодняшняя дата: СЕГОДНЯ()

Функция =СЕГОДНЯ() возвращает текущую дату и автоматически обновляется при каждом пересчёте файла — удобно для формул, которые должны показывать актуальный срок или возраст «на сегодня», а не на момент, когда файл создавался.

Разница между двумя датами

Самый простой способ — прямое вычитание: =B2-A2 вернёт количество дней между датами в ячейках A2 и B2. Если результат отображается как дата, а не число — дело в формате ячейки, его нужно вручную сменить на числовой.

Когда нужна разница не в днях, а в полных годах, месяцах или их комбинации — на помощь приходит функция РАЗНДАТ (в английской версии DATEDIF):

• =РАЗНДАТ(A2;B2;"y") — полное количество лет между датами;
• =РАЗНДАТ(A2;B2;"m") — полное количество месяцев;
• =РАЗНДАТ(A2;B2;"d") — полное количество дней;
• =РАЗНДАТ(A2;B2;"ym") — количество месяцев без учёта полных лет (остаток).

Расчёт стажа на конкретную дату

Типичная кадровая задача — посчитать стаж сотрудника в годах и месяцах на текущий момент. Комбинация РАЗНДАТ с разными кодами единиц измерения даёт «человекочитаемый» результат:

=РАЗНДАТ(A2;СЕГОДНЯ();"y")&" лет "&РАЗНДАТ(A2;СЕГОДНЯ();"ym")&" мес."

Здесь A2 — дата приёма на работу, а результат автоматически пересчитывается каждый раз при открытии файла, потому что опирается на СЕГОДНЯ().

Срок до события и напоминания

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

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

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

Рабочие дни между датами

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

• ЧИСТРАБДНИ(нач_дата; кон_дата; [праздники]) — считает количество рабочих дней между двумя датами, исключая субботы и воскресенья;
• РАБДЕНЬ(нач_дата; количество_дней; [праздники]) — находит дату, отстоящую от начальной на заданное число рабочих дней вперёд или назад.

Дата через N месяцев или конец месяца

• ДАТАМЕС(нач_дата; число_месяцев) — возвращает дату, отстоящую от начальной на заданное число месяцев вперёд (положительное значение) или назад (отрицательное). Удобно для расчёта даты окончания годового договора или отпуска;
• КОНМЕСЯЦА(нач_дата; число_месяцев) — то же самое, но возвращает последний день нужного месяца, а не то же число.

Частые ошибки при работе с датами

• Дата введена как текст — если формат ячейки не распознал ввод как дату, формулы с датами перестают работать корректно; распознанная дата в Excel обычно выравнивается по правому краю ячейки, текст — по левому;
• Спутан порядок день/месяц — при вводе даты вручную легко перепутать региональный формат (день/месяц или месяц/день), особенно при копировании данных из источников с другими региональными настройками;
• Забыт формат ячейки после вычислений — результат вычитания дат иногда наследует формат «Дата» вместо числового, из-за чего число дней отображается как малопонятная дата.

Часто задаваемые вопросы

Что дальше

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