Как определить последний рабочий день месяца(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
• При помощи функции пользователя на VBA
• При помощи PowerQuery

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

'---------------------------------------------------------------------------------------
' 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). Если указано как ЛОЖЬ, то при вычислении последнего рабочего дня не будут учитываться выходные. В этом случае они обязательно должны быть перечислены в списке Праздники. Если они не перечислены - то все дни недели считаются рабочими, кроме праздников. Если указано как ИСТИНА, то в качестве выходных будут применены те дни, которые установлены для календаря в операционной системе. Для русской локализации это как правило Суббота и Воскресенье.

Если список праздников еще не преобразован в умную таблицу, то на листе Праздники выделяем все ячейки с датами -переходим на вкладку Вставка(Insert) –Таблица(Table). Снимаем галку с пункта "Таблица с заголовками" -Ок:

После этого можно на вкладке Главная нажать Закрыть и загрузить. Будет создан новый лист с одной ячейкой - датой последнего рабочего дня.
Чтобы приведенный запрос работал корректно, необходимо назвать столбец с праздничными датами "Дата выходного дня". Либо изменить это название в самом запросе.
Если нужен последний рабочий день на конкретную дату, то необходимо убрать первую строку(dNow = Date.From(DateTime.LocalNow()),) и убрать слеши вначале следующей строки:
let
dNow = Date.FromText("2017-09-19"),
lastDay = Date.EndOfMonth(dNow),
WeekD = Date.DayOfWeek(lastDay,1),
dd = if WeekD < 5 then lastDay else Date.AddDays(lastDay,-(WeekD-4)), //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
Если выходные не надо учитывать(чтобы суббота и воскресенье не рассчитывались автоматом в зависимости от дня недели), то запрос будет выглядеть так:
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

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

  ПоследнийРабочийДень.xlsm (50,7 KiB, 679 скачиваний)

    Видеоуроки

Поиск по меткам