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

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY (DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

Эта формула возвращает дату только с понедельника по пятницу и всегда последний такой день в месяце, представленном датой в A1. Для некоторых целей вам может потребоваться знать, какая последняя пятница каждого месяца. Это легко определить с помощью этой формулы:

=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))+(WEEKDAY(DATE (YEAR(A1),MONTH(A1)+1,0))>5)*7-1

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

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

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

Function LastWorkDay(lRawDate As Long, _     Optional rHolidayList As Range, _     Optional bFriday As Boolean = False) As Long

LastWorkDay = DateSerial(Year(lRawDate), _       Month(lRawDate) + 1, 0) - 0     If bFriday Then         LastWorkDay = MakeItFriday(LastWorkDay)

Else         LastWorkDay = NoWeekends(LastWorkDay)

End If

If Not rHolidayList Is Nothing Then         Do Until myMatch(LastWorkDay, rHolidayList) = 0             LastWorkDay = LastWorkDay - 1             If bFriday Then                 LastWorkDay = MakeItFriday(LastWorkDay)

Else                 LastWorkDay = NoWeekends(LastWorkDay)

End If         Loop     End If End Function
Private Function myMatch(vValue, rng As Range) As Long     myMatch = 0     On Error Resume Next     myMatch = Application.WorksheetFunction _         .Match(vValue, rng, 0)

On Error GoTo 0 End Function
Private Function NoWeekends(lLastDay As Long) As Long     NoWeekends = lLastDay     If Weekday(lLastDay) = vbSunday Then _       NoWeekends = NoWeekends - 2     If Weekday(lLastDay) = vbSaturday Then _       NoWeekends = NoWeekends - 1 End Function
Private Function MakeItFriday(lLastDay As Long) As Long     MakeItFriday = lLastDay     While Weekday(MakeItFriday) <> vbFriday         MakeItFriday = MakeItFriday - 1     Wend End Function

Обратите внимание, что включены три закрытые функции. Эти функции вызываются из основной функции LastWorkDay. Первый, myMatch, является «оболочкой» для обычного метода Match. Это использование включено из-за необходимой обработки ошибок.

Вторая функция, NoWeekdends, используется для резервного копирования даты до предыдущей пятницы, если это просто суббота или воскресенье. Функция MakeItFriday используется, чтобы гарантировать, что дата всегда будет пятницей.

Чтобы использовать эту определяемую пользователем функцию из рабочего листа, используйте ее в формуле, например:

=LastWorkDay(A1, HolidayList, TRUE)

Первый параметр (A1) — это дата, которую нужно оценить. Второй параметр (HolidayList) — это необязательный список праздничных дат. Как показано здесь, предполагается, что HolidayList — это именованный диапазон на листе.

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

Последний параметр также необязателен; это может быть ИСТИНА или ЛОЖЬ.

(Значение по умолчанию, если оно не указано, — ЛОЖЬ.) Если для этого параметра установлено значение ИСТИНА, функция всегда возвращает последнюю пятницу месяца. Если этот параметр имеет значение ИСТИНА и указан HolidayList, функция возвращает последнюю непраздничную пятницу месяца.

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (10085) применим к Microsoft Excel 2007 и 2010. Вы можете найти версию этого совета для более старого интерфейса меню Excel здесь:

link: / excel-The_Last_Business_Day [Последний рабочий день].