Как определить последний рабочий день месяца(Excel, VBA, PowerQuery)

Как определить последний рабочий день месяца(Excel, VBA, PowerQuery)

Проблема получения последнего дня месяца довольно распространена и очень просто решается. Начиная с Excel 2007 можно без всяких доп.манипуляций использовать функцию КОНМЕСЯЦА (EOMONTH) : =КОНМЕСЯЦА(ТДАТА();0) =EOMONTH(NOW(),0) ТДАТА (NOW) - возвращает текущую дату. Вместо неё можно указать любую дату и последняя дата месяца будет возвращена для указанной даты. Для версий 2003 и ранее для использования КОНМЕСЯЦА (EOMONTH) необходимо для начала подключить надстройку Пакет анализа или же использовать чуть менее понятную формулу: =ДАТА(ГОД(ТДАТА());МЕСЯЦ(ТДАТА())+1;0) =DATE(YEAR(NOW()),MONTH(NOW())+1,0) На самом деле данная формула проста, если разобраться детальнее. Функция ДАТА записывает в ячейку дату, на основании указанного года, месяца и дня. Мы для года берем год от текущей даты, далее берем месяц текущей даты и к месяцу прибавляем 1, чтобы получить следующий месяц. А потом для дня указываем 0, что заставляет Excel сдвинуть первую дату следующего месяца на один день назад. Таким образом и получается последняя дата месяца. Пошагово это выглядит так(если считать, что сегодня "22.10.2017"): =ДАТА(ГОД(ТДАТА());МЕСЯЦ(ТДАТА())+1;0) => =ДАТА(ГОД("22.10.2017");МЕСЯЦ("22.10.2017")+1;0) => =ДАТА(2017;10+1;0) => =ДАТА(2017;11;0) здесь уже получится 01.11.2017, но 0 в качестве дня заставляет вернуться на день назад => =31.10.2017

Но есть более сложная проблема - получить дату последнего рабочего дня месяца. А это уже не так просто. Встроенных функций(вроде КОНМЕСЯЦА) для этого в Excel нет. А это значит опять придется танцевать с бубном. Сразу возникает вопрос: а зачем это вообще может потребоваться? Например, для поиска счетов, отгрузка по которым производилась в последний рабочий день месяца, а оплаты(или документы на отгрузку) по некоторым пришли только в начале следующего. Так же такую функцию будет удобно применять для автоматического формирования платежек или накладных, если они должны быть оформлены именно последним рабочим днем месяца. Чтобы получить именно корректную дату следует так же учесть и тот факт, что есть дни праздничные. И их тоже надо как-то учесть. Для этого я буду использовать отдельный лист Праздники , в столбце А которого записаны праздничные дни на пару лет. Также в этом диапазоне перечислены и все выходные. Сделано для более корректного подсчета дат, т.к. праздники могут быть перенесены, в результате чего рабочими становятся субботы или воскресенья. Если их не учитывать, то расчет будет неверным.

Из спортивного интереса создать такую функцию решил тремя способами:

Последний рабочий день при помощи стандартных функций Excel В ходе некоторых экспериментов и манипуляций появилась такая формула: =РАБДЕНЬ( ЕСЛИ(ДЕНЬНЕД(КОНМЕСЯЦА( B4 ;0);2) B4 ;0); КОНМЕСЯЦА( B4 ;0)-(ДЕНЬНЕД(КОНМЕСЯЦА( B4 ;0);2)-5)); 0; Праздники!$A$2:$A$827) =WORKDAY( IF(WEEKDAY(EOMONTH(B4,0),2) где Праздники!$A$2:$A$827 - ссылка на диапазон дат с праздниками Здесь я так же использую КОНМЕСЯЦА для быстрого определения последней даты месяца. Функция ДЕНЬНЕД (WEEKDAY) возвращает номер дня недели для указанной даты и нужна для того, чтобы определить - является последний день недели субботой или воскресеньем. Если нет, то возвращаем дату как есть, если это суббота или воскресенье - то отнимаем от даты либо 1 день, либо 2(если это СБ - то 1, если ВСК - то 2). И только после этого применяем к полученной дате функцию РАБДЕНЬ (WORKDAY) , которая для последней даты месяца определяет, является ли она праздничной(на основании списка праздников Праздники!$A$2:$A$827 ). Если дата праздничная - то она уменьшается до тех пор, пока не достигнет рабочего дня. РАБДЕНЬ не принимает в расчет выходные, поэтому нам и нужны функции ЕСЛИ и ДЕНЬНЕД, прежде чем применить РАБДЕНЬ.

Однако сразу хочу оговорить, что в конкретно моем случае вычисление выходных дней при помощи ЕСЛИ и ДЕНЬНЕД несколько избыточно, т.к. в диапазоне с праздниками помимо непосредственно праздников у меня уже учтены все выходные дни. Т.е. по сути формула могла бы быть намного проще: =РАБДЕНЬ(КОНМЕСЯЦА( B4 ;0); 0; Праздники!$A$2:$A$827) =WORKDAY(EOMONTH(B4,0), 0, Праздники!$A$2:$A$827) Но я все равно решил именно в статье привести формулу, которая смотрит и на дни недели, т.к. требования могут быть разными.

Т.к. Microsoft все же немного заботится о нас, то для пользователей версий Excel 2010 и более новых, в случае необходимости учитывать день недели прямо в формуле, можно использовать функцию РАБДЕНЬ.МЕЖД: =РАБДЕНЬ.МЕЖД(КОНМЕСЯЦА(B4;0);0;1;Праздники!$A$2:$A$827) =WORKDAY.INTL(EOMONTH(B4,0),0,1,Праздники!$A$2:$A$827) РАБДЕНЬ.МЕЖД[дата; кол-во дней; выходные; праздники] дата - дата, от которой необходимо отсчитать указанное кол-во дней кол-во дней - количество дней, которые надо прибавить или отнять(отриц. число - отнимает дни от даты) от указанной даты выходные - 1 = Суббота и Воскресенье, 2 - Воскресенье и Понедельник и т.д. Полный перечень есть в справке для функции и в выпадающей подсказке к функции при вводе. праздники - ссылка на диапазон дат с праздниками Достаточно указать последнюю дату месяца, указать для неё сдвиг на 0 дней, указать тип выходных и ссылку на диапазон дат с праздниками. Все уже сделано и все гораздо проще.

Последний рабочий день при помощи функции пользователя на VBA Если использование VBA не является для вас проблемой, то можно применить функцию пользователя(UDF) для расчета последнего рабочего дня:

'--------------------------------------------------------------------------------------- ' Author : The_Prist(Щербаков Дмитрий) ' Профессиональная разработка приложений для MS Office любой сложности ' Проведение тренингов по MS Excel ' http://www.excel-vba.ru ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' Purpose: Функция получения последнего рабочего дня на основании указанной даты и списка праздников '--------------------------------------------------------------------------------------- Option Explicit Function ПоследнийРабочийДень(Дата As Date, Optional Праздники As Range = Nothing, Optional СистемныеВыходные As Boolean = False) Dim dd As Date, dres As Date Dim lWeekDay As Long dres = DateSerial(Year(Дата), Month(Дата) + 1, 0) If СистемныеВыходные Then lWeekDay = Weekday(dres, vbUseSystemDayOfWeek) If lWeekDay < 6 Then dres = dres Else dres = dres - (lWeekDay - 5) End If End If Do While (IsHoliday(dres, Праздники)) dres = dres - 1 Loop ПоследнийРабочийДень = dres End Function 'Функция поиска указанной даты среди праздничных дат ' dd - искомая дата ' Holidays - диапазон с праздничными днями Function IsHoliday(ByVal dd As Date, Optional Holidays As Range = Nothing) Dim lWeekDay As Long Dim x, rr As Range If Holidays Is Nothing Then Exit Function For Each x In Holidays.Value If IsDate(x) Then If CDate(x) = dd Then IsHoliday = True Exit Function End If End If Next End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) . Синтаксис функции: =ПоследнийРабочийДень( B4 ;Праздники!$A$2:$A$827;0)

  • Дата( B4 ) - непосредственно дата или ссылка на ячейку с датой, последней рабочий день для которой необходимо вычислить. Будет рассчитан последний рабочий день для месяца указанной даты.
  • Праздники() - ссылка на диапазон ячеек, содержащих даты праздничных дней.
  • СистемныеВыходные(0) - ИСТИНА (TRUE или 1) или ЛОЖЬ (FALSE или 0) . Если указано как ЛОЖЬ, то при вычислении последнего рабочего дня не будут учитываться выходные. В этом случае они обязательно должны быть перечислены в списке Праздники. Если они не перечислены - то все дни недели считаются рабочими, кроме праздников. Если указано как ИСТИНА, то в качестве выходных будут применены те дни, которые установлены для календаря в операционной системе. Для русской локализации это как правило Суббота и Воскресенье.

В правой части окна редактора изменяем название запроса(скорее всего там Таблица1) на Праздники:

Переходим на вкладку Главная, раскрываем пункт Закрыть и загрузить и выбираем Закрыть и загрузить в. :

В появившемся окне выбираем Только создать подключение и нажимаем Ок:

Теперь переходим на вкладку Данные(Data) -Получить данные -Из других источников -Пустой запрос. Переходим в расширенный редактор(вкладка Главная -Расширенный редактор):

и вставляем туда следующий текст: let dNow = Date.From(DateTime.LocalNow()), //dNow = Date.FromText("2017-09-19"), lastDay = Date.EndOfMonth(dNow), WeekD = Date.DayOfWeek(lastDay,1), dd = if WeekD let dAdd = value, select_rows = Table.SelectRows(Праздники, each Record.Field(_, "Дата выходного дня") = dAdd), count_rows = Table.RowCount(select_rows), result = Date.AddDays(value,-count_rows) in result, res_d = Table.FromValue(coun_dif(dd)) in res_d

После этого можно на вкладке Главная нажать Закрыть и загрузить. Будет создан новый лист с одной ячейкой - датой последнего рабочего дня. Чтобы приведенный запрос работал корректно, необходимо назвать столбец с праздничными датами "Дата выходного дня" . Либо изменить это название в самом запросе. Если нужен последний рабочий день на конкретную дату, то необходимо убрать первую строку( dNow = Date.From(DateTime.LocalNow()), ) и убрать слеши вначале следующей строки: let dNow = Date.FromText("2017-09-19"), lastDay = Date.EndOfMonth(dNow), WeekD = Date.DayOfWeek(lastDay,1), dd = if WeekD let dAdd = value, select_rows = Table.SelectRows(Праздники, each Record.Field(_, "Дата выходного дня") = dAdd), count_rows = Table.RowCount(select_rows), result = Date.AddDays(value,-count_rows) in result, res_d = Table.FromValue(coun_dif(dd)) in res_d Если выходные не надо учитывать(чтобы суббота и воскресенье не рассчитывались автоматом в зависимости от дня недели), то запрос будет выглядеть так: let dNow = Date.FromText("2017-09-19"), dd = Date.EndOfMonth(dNow), coun_dif = (value as date) as date => let dAdd = value, select_rows = Table.SelectRows(Праздники, each Record.Field(_, "Дата выходного дня") = dAdd), count_rows = Table.RowCount(select_rows), result = Date.AddDays(value,-count_rows) in result, res_d = Table.FromValue(coun_dif(dd)) in res_d

Реализацию всех приведенных решений можно скачать в приложенном файле Скачать файл:

📎📎📎📎📎📎📎📎📎📎