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

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

Итак, если у вас есть дата поступления студента в A1 и дата окончания в A2, единственное, что вам нужно сделать, это создать список праздников. Вы можете сделать это, просто начав вводить праздники в диапазон ячеек. Введите по одной дате в ячейку, а затем выберите диапазон. Задайте имя для ссылки на диапазон, например MyHolidays.

Затем вы можете использовать следующую формулу:

=(NETWORKDAYS(A1, DATE(YEAR(A1), MONTH(A1) + 1,0), MyHolidays) >= 5)

+ (NETWORKDAYS(DATE(YEAR(A2), MONTH(A2),1), A2, MyHolidays) >= 5)

+ DATEDIF(DATE(YEAR(A1), MONTH(A1) + 1, 1), DATE(YEAR(A2), MONTH(A2), 1), "m")

Формула довольно длинная и требует некоторого изучения. Обратите внимание, что помимо функции ЧИСТРАБДНИ, она также использует функцию РАЗНДАТ, которая используется для определения разницы между двумя датами и возврата интервала разными способами. В этом случае он используется с буквой «m»

параметр, что означает, что он возвращает интервал в виде количества месяцев — именно то, что нужно Ольге.

Первая часть формулы (первое использование функции ЧИСТРАБДНИ) используется для определения количества дней между датой входа (в ячейке A1) и концом месяца, в котором происходит дата входа. Если это значение больше или равно 5 (отсечка Ольги), то возвращается значение 1, поскольку это считается одним оплачиваемым месяцем.

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

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9514) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Calculating_Months_for_Billing_Purposes [Расчет месяцев для целей выставления счетов].