Автоматизация реестра договоров и сроков их действия в Excel

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

В ячейку J2 введите формулу:
=ЕСЛИ(G2="";"";G2-СЕГОДНЯ())

Формула вычитает текущую дату из даты окончания договора.

Результат интерпретируется следующим образом:

• положительное число — до окончания срока осталось указанное количество дней;
• ноль — дата окончания приходится на сегодня;
• отрицательное число — дата окончания уже прошла;
• пустая ячейка — дата окончания не указана.

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

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

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

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

Используйте формулу:
=ЕСЛИ(G2="";"Не указана

дата";ЕСЛИ(G2<СЕГОДНЯ();"Истёк";ЕСЛИ(G2=СЕГОДНЯ();"Окончание сегодня";ЕСЛИ(G2<=СЕГОДНЯ()+30;"Истекает в течение 30 дней";"Действует"))))

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

Например:

Пример рассчитан на 28 октября 2026 года.

Если в реестре есть бессрочные договоры, добавьте отдельный столбец «Тип срока» или «Бессрочный» и предусмотрите для них отдельное условие. Не следует автоматически считать договор истёкшим только потому, что поле даты окончания не заполнено.

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

Например, можно настроить следующие правила для столбца с датами окончания:

• Красный: срок договора истёк.
• Оранжевый: до окончания осталось 7 дней или меньше.
• Жёлтый: до окончания осталось от 8 до 30 дней.
• Зелёный: до окончания больше 30 дней.

Чтобы выделить просроченные даты, выберите диапазон G2 и создайте правило с формулой:
=И(G2<>"";G2<СЕГОДНЯ())

Для договоров, заканчивающихся в течение ближайших семи дней:
=И(G2>=СЕГОДНЯ();G2<=СЕГОДНЯ()+7)

Для срока от 8 до 30 дней:
=И(G2>СЕГОДНЯ()+7;G2<=СЕГОДНЯ()+30)

При настройке правил укажите соответствующие цвета и проверьте порядок применения условий.

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

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

В версиях Excel с поддержкой динамических массивов можно использовать функцию ФИЛЬТР.
Предположим, исходный реестр находится в диапазоне A2, а даты окончания — в столбце G.

Чтобы вывести договоры, заканчивающиеся в течение следующих 30 дней, используйте:
=ФИЛЬТР(A2:J1000;(G2:G1000>=СЕГОДНЯ())*(G2:G1000<=СЕГОДНЯ()+30);"Нет договоров на продление")

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

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

Если функция ФИЛЬТР недоступна в вашей версии Excel, можно воспользоваться стандартными фильтрами таблицы и отобрать записи по статусу.

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

Для начала добавьте столбец «Контроль» и используйте формулу:
=ЕСЛИ(G2="";"Проверить дату";ЕСЛИ(G2<СЕГОДНЯ();"Требуется проверка";ЕСЛИ(G2<=СЕГОДНЯ()+7;"Срочно: до 7 дней";ЕСЛИ(G2<=СЕГОДНЯ()+30;"Подготовить продление";"Контроль по графику"))))

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

Если нужны автоматические письма или уведомления по расписанию, можно дополнительно рассмотреть интеграцию с Power Automate или другими средствами автоматизации, доступными в используемой корпоративной среде.

При этом следует учитывать, что отправка уведомлений зависит от настроек, подключений и разрешений соответствующих сервисов.

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

В отдельном столбце укажите Ф. И. О. ответственного, а в другом — следующий шаг: подготовить дополнительное соглашение, согласовать новые условия, запросить документы или уведомить контрагента.

Например:

Чтобы уменьшить количество ошибок при заполнении, настройте выпадающий список ответственных сотрудников через вкладку «Данные» → «Проверка данных».

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

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

Дата окончания не всегда означает прекращение обязательств. После истечения срока действия отдельные обязательства могут сохраняться в зависимости от условий договора и применимого законодательства. Автоматический статус Excel не заменяет юридическую оценку.

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

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

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

Автоматизация реестра договоров требует уверенной работы с формулами, логическими условиями, фильтрами, условным форматированием и структурированными таблицами.

Учебный центр «Точка Роста» предлагает обучение Excel и профессиональные курсы по бухгалтерскому учёту. Практическая направленность обучения может быть полезна специалистам, которые хотят систематизировать работу с документами, повысить точность расчётов и сократить объём ручных операций.

Если ваша задача — освоить автоматизацию реестров и контроль сроков, перед записью уточните актуальную программу курса и наличие соответствующих практических заданий.

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

С помощью функций СЕГОДНЯ, ЕСЛИ и ФИЛЬТР, а также условного форматирования можно создать удобную систему контроля без программирования. Для более сложных задач реестр можно дополнить сводными таблицами, Power Query и инструментами автоматической отправки уведомлений.

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