Excel на ИЗИ: ✅ Приемы эффективной работы в Microsoft Excel

4457 подписчиков

📌 Функции Excel для работы с датами (Урок 11)

23 мая 202223 мая 2022

2906

4 мин

Оглавление

⏩ Расчет количества дней по отношению к текущей дате.
⏩ Определение количества лет
⏩ Расчет рабочих дней

Ребята, всем привет! 👋 Продолжаем серию уроков посвященных функциям Excel. И сегодня пришло время поговорить о функциях для работы с датами.

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

🔔 Что важно! Excel может работать с датами, начиная с 1 января 1900 г. (даты до 1900 г. воспринимаются как текст). Эта дата соответствует положительному числу 1, каждая последующая дата так же соответствует целому.

❗️ ❗️ ❗️ Каждая дата соответствует целому положительному числу:

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

Для наглядности рассмотрим вычисления между датами на примерах 👇

⏩ Расчет количества дней по отношению к текущей дате.

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

📚 Синтаксис функции:

СЕГОДНЯ() – вставка текущей даты в формате даты.
TODAY()

=СЕГОДНЯ() -- >> 22.05.2022

🔔 Что важно!

У функции СЕГОДНЯ() нет аргументов. Значение даты подставляется из текущих настроек даты операционной системы. Обновление происходит при открытии файла, печати данных и вводе данных на листе.
Для принудительного обновления значений можно нажать клавишу F9
Если необходимо сделать расчет множества значений, то рекомендуется добавить функцию СЕГОДНЯ() в одну ячейку, и в расчетах использовать ссылку на адрес этой ячейки (для удобства этой ячейке можно присвоить имя).

⏩ Определение количества лет

Для решения задач по определению количества лет, можно воспользоваться функцией ДОЛЯГОДА().

📚 Синтаксис функции:

ДОЛЯГОДА(Нач_дата;Кон_дата;Базис) – определяет долю году, которую составляет количество дней между начальной и конечной датой.
YEARFRAC(Start_date;End_date;Basis)

Для определения полных лет, результат следует обработать функциями округления, например, ЦЕЛОЕ().

📝 ПРИМЕР: Определим стаж работника на текущую дату:

=ЦЕЛОЕ(ДОЛЯГОДА(B3;$B$1)), где

ДОЛЯГОДА(B3;$B$1) - возвращает долю года, которую составляет количество дней между двумя датами (начальной и конечной)
ЦЕЛОЕ(ДОЛЯГОДА(B3;$B$1)) - округляет число до ближайшего меньшего целого( в примере, 26, 08333.. -- >> 26)

⏩ Расчет рабочих дней

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

При решении задач расчета именно рабочих дней используется функция РАБДЕНЬ.МЕЖД().

📚 Синтаксис функции:

РАБДЕНЬ.МЕЖД(Нач_дата;Число_дней;Выходные;Праздники) – определение даты, отстоящей на заданное число рабочих дней вперед или назад от начальной даты.
WORKDAY(Start_date;Days;Weekend;Holidays)

Описание аргументов:

👉 Нач_дата [Start_date] – дата, относительно которой ведется расчет.

👉 Число_дней [Days] – количество не выходных и не праздничных дней до или после начальной даты.

👉 Выходные [Weekend] – необязательный аргумент. Если значение не заполняется, то считается, что выходные дни – суббота и воскресенье.

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

Значение 1 – нерабочие дни, а 0 – рабочие дни. Например, 1000001 означает, что выходными днями являются понедельник и воскресенье.

Выбрать условие Вы можете также из выпадающего списка:

👉 Праздники [Holidays] – необязательный аргумент. Одно или несколько значений дат, которые в рабочие дни являются выходными (государственные праздники).

📝 ПРИМЕР: Определить дату окончания стажировки сотрудников.

Рабочими днями на неделе считать: понедельник, вторник, среда, четверг, пятница, суббота.

Учесть праздничные дни.

=РАБДЕНЬ.МЕЖД(B3;(C3-1);11;$F$2:$F$5)

⏩ Подсчет количества календарных дней между двумя указанными датами

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

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

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

📚 Синтаксис функции:

ЧИСТРАБДНИ.МЕЖД(Нач_дата;Кон_дата;Выходные;Праздники) – определение полных рабочих дней между двумя указанными датами.
NETWORKDAYS(Start_date;End_date; Weekend;Holidays)

Описание аргументов:

👉 Нач_дата [Start_date] – начальная дата периода.

👉 Кон_дата [End_date] – конечная дата периода

👉 Выходные [Weekend] – необязательный аргумент. Если значение не заполняется, то считается, что выходные дни – суббота и воскресенье (аналогично вышеприведенному примеру)

👉 Праздники [Holidays] – необязательный аргумент. Одно или несколько значений дат, которые в рабочие дни являются выходными, например, государственные праздники.

📝 ПРИМЕР: Решим обратную задачу. Известна дата приема и дата окончания стажировки. Определить длительность стажировки.

Рабочими днями на неделе считать: понедельник, вторник, среда, четверг, пятница, суббота.

Учесть праздничные дни.

=ЧИСТРАБДНИ.МЕЖД(B3;D3;11;$G$2:$G$5)

На этом сегодня все. Продолжение следует...

Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.