Продолжаю серию материалов по работе с датами в Excel.
В первой части я попытался раскрыть тему конвертирования "кривых" данных в формат дат. Сегодня поговорим о том, как можно прибавлять даты, считать календарные и рабочие дни от указанной даты, проверять вхождение даты в диапазон дат, а также определять текущую дату.
Делитесь этим материалом в своих социальных сетях, подписывайтесь на канал и ставьте лайки.
Спасибо!
Разность дат
Поскольку Excel хранит все даты в виде чисел - вычислить количество дней между двумя датами можно простым вычитанием:
Аналогичным образом работает функция ДНИ(кон_дата; нач_дата). У функции ДНИ() есть преимущество, по сравнению с простым вычитанием дат - в случае, если переданные ей аргументы имеют текстовый формат (например, имеют лишний пробел) - функция преобразует их в дату.
Рабочие дни от заданной даты
Чтобы определить дату через n дней от заданной даты, достаточно прибавить или удалить число дней. Если же стоит более сложная задача, подразумевающая расчет рабочих рабочих дней в одну или другую сторону от указанной даты, то на помощь приходит встроенная функция РАБДЕНЬ():
РАБДЕНЬ(нач_дата;количество_дней;[праздники])
Функция возвращает дату, отстоящую на заданное количество дней от начальной даты вперед или назад (если назад, то указываем значение со знаком "-").
По-умолчанию функция считает за выходные дни субботу и воскресенье, кроме того у нее есть параметр "праздники", который позволяет включить другие нерабочие дни - например, государственные праздники. Достаточно создать массив с датами (это либо данные в формате дат, либо результаты вычислений функции ДАТА()) и указать его в параметр "праздники".
Взять данные для параметра "праздники" можно из производственного календаря на год, в рамках которого вы делаете расчеты. Они публикуются ежегодно и версия Excel есть на сайте СПС Консультант. Все даты с праздниками можно скопировать на отдельный лист и дать диапазону удобное имя.
Расширенной версией функции рабдень РАБЕНЬ() является функция РАБДЕНЬ.МЕЖД():
РАБДЕНЬ.МЕЖД(нач_дата;количество_дней;[выходной];[праздники])
Отличительный признаком РАБДЕНЬ.МЕЖД() является параметр "выходной" - он позволяет задать функции дни недели, которые необходимо считать выходными. Это числовое значение от 1 до 17, каждое из которых значит определенное сочетание дней, например: 1 или опущен - Суббота, воскресенье (тогда это будет аналог функции РАБДЕНЬ()), 2 - Воскресенье, понедельник, 3 - Понедельник, вторник. Более подробную информацию по значениям параметра "выходной" можно найти во встроенной справке, которую можно вызвать нажатием клавиши F1 или сочетанием клавиш Fn+F1 (MAC).
При помощи функнции РАБДЕНЬ() можно построить платежный календарь. Например, его упрощенная версия может выглядеть примерно так:
Можно усложнить задачу - сделать колонку с типом расчета от даты начала работ, в зависимости от условий контракта с константами: "раб.д." и "календ.д." и уже при помощи функции ЕСЛИ() выбрать правильный алгоритм расчета. На другом листе можно сделать таблицу с датами и при помощи функции СУММЕСЛИ() посчитать размер платежей на определенную дату по всем контрактам.
Нахождение даты в заданном диапазоне дат (времени)
Иногда бывает необходимо понять, попадает ли заданная дата в нужный отрезок или нет. Например, перед вами выгрузка из HR-системы с сотрудниками вашей компании, в которой на против каждого ФИО есть дата приема. Вам нужно из всех сотрудников выбрать только тех, кто был принят в период с 01.01.2015 по 31.01.2016. Такую задачу можно легко решить при помощи встроенной логической функции И():
Логическая функция И проверят, все ли логические выражения внутри нее имеют значение "Истина", если какое-то значение имеет "Ложь", то функция вернет значение "ЛОЖЛЬ". Соответственно, мы проверяем, что дата приема больше или равна 01.01.2015 и меньше или равна 31.01.2016.
Обратите внимание, что в Excel больше или равно обозначается сочетанием символов ">=", а меньше или равно соответственно "<=".
Если вы хотите в колонке "Проверка" иметь иные значения, чем Истина и Ложь, вы можете воспользоваться функцией ЕСЛИ(): ЕСЛИ(И(выражение); Значение если Истина, значение если ЛОЖЬ).
Аналогичным образом можно проверять другие условия с временными интервалами.
Текущая дата
В завершении напомню, как можно получить текущую дату в Excel. Для этой цели есть специальная функция СЕГОДНЯ(). Она возвращает текущую (системную) дату.
Также есть функция ТДАТА() которая возвращает текущую дату и время.
Обратите внимание, что функции СЕГОДНЯ() и ТДАТА() возвращают системные значения даты и времени, т.е, те, которые установлены у вас на компьютере
Функции вызываются без передачи каких-либо параметров, в нужной ячейке достаточно написать =СЕГОДНЯ() или =ТДАТА().
При открытии рабочей книги функции будут менять свои значения (за исключением принудительного отключения пересчета формул).
Данные функции имеют очень широкое применение, например, они могут быть использованы в уже упомянутом платежном календаре для пересчета задолженности на текущую дату.
