Расчет рабочего времени за период, ограниченный двумя датами, в течение одного года с помощью кода VBA Excel. Функция для расчета рабочего времени.
Календарь рабочего времени
Для расчета рабочего времени по двум датам нам понадобится производственный мини-календарь на год, состоящий из двух строк:
Создавать такой календарь вручную, не считая протяжку дат, довольно долго и нудно. Поэтому мы создадим производственный мини-календарь программно.
Для создания календаря рабочего времени из кода VBA Excel сначала следует запустить процедуру DaysOfYear, которая заполнит строку дат:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub DaysOfYear() Dim y As String, c1 As Range, i As Integer 'Указываем год, для которого нужна строка дат y = "2023" 'Указываем ячейку, в которой будет первый день года Set c1 = Cells(1, 2) 'В первую ячейку строки с датами вставляем первый день года c1 = CDate("01.01." & y) 'Задаем формат отображения даты c1.NumberFormat = "dd.mm.yy" 'Заполняем выбранную строку со 2 дня до конца года Do While c1.Offset(0, i) <> CDate("31.12." & y) c1.Offset(0, i + 1) = c1.Offset(0, i) + 1 c1.Offset(0, i + 1).NumberFormat = "dd.mm.yy" i = i + 1 Loop 'Присваиваем диапазону дат имя Range(c1, c1.End(xlToRight)).Name = "Год" & y End Sub |
Для создания строки дат потребуется указать только два параметра: год и ячейку, с которой начинается эта строка.
Процедура DaysOfYear запускается из редактора VBA Excel и работает около минуты. Ускорить ее можно, используя массив, который заполняется датами и затем значения его элементов вставляются в диапазон дат.
В конце процедуры диапазону дат присваивается имя, по которому мы будем обращаться к нему в дальнейшем.
Заполнение строки часов
Допустим, нам нужен календарь с пятидневной 40-часовой рабочей неделей, в котором необходимо указать 8 часов для будничных дней и 0 часов — для выходных.
Заполняем строку рабочего времени из кода VBA Excel с помощью процедуры WorkingTimeDay, которая в ячейки строки времени под ячейками с субботой и воскресеньем вставляет 0, а под ячейками с буднями — 8:
1 2 3 4 5 6 7 8 9 10 |
Public Sub WorkingTimeDay() Dim c As Range For Each c In Range("Год2023") If Weekday(c, vbMonday) = 6 Or Weekday(c, vbMonday) = 7 Then c.Offset(1, 0) = 0 Else c.Offset(1, 0) = 8 End If Next End Sub |
Ну и для полной достоверности, следует исключить из группы рабочих праздничные дни, для чего запускаем из редактора VBA Excel следующую процедуру:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Sub Holidays2023() 'Нерабочие праздничные дни в 2023 году: 'с 1 по 8 января — 8 дней; 'с 23 по 26 февраля — 4 дня; '8 марта; 'с 29 апреля по 1 мая — 3 дня; 'с 6 по 9 мая — 4 дня; 'с 10 по 12 июня — 3 дня; 'с 4 по 6 ноября — 3 дня. Dim c As Range For Each c In Range("Год2023") Select Case c Case CDate("01.01.2023") To CDate("08.01.2023") c.Offset(1, 0) = 0 Case CDate("23.02.2023") To CDate("26.02.2023") c.Offset(1, 0) = 0 Case CDate("08.03.2023") c.Offset(1, 0) = 0 Case CDate("29.04.2023") To CDate("01.05.2023") c.Offset(1, 0) = 0 Case CDate("06.05.2023") To CDate("09.05.2023") c.Offset(1, 0) = 0 Case CDate("10.06.2023") To CDate("12.06.2023") c.Offset(1, 0) = 0 Case CDate("04.11.2023") To CDate("06.11.2023") c.Offset(1, 0) = 0 End Select Next End Sub |
Процедура Holidays2023 ищет в диапазоне дат праздничные дни и в соответствующие им ячейки строки рабочего времени вставляет нули.
Расчет рабочего времени
Расчет рабочего времени за период, ограниченный двумя датами, будем производить с помощью функции WorkingPeriodHours:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Public Function WorkingPeriodHours(date1, date2) If date2 < date1 Or Not IsDate(date1) Or Not IsDate(date2) Then WorkingPeriodHours = "Укажите период" Exit Function End If Dim i As Integer, d1 As Integer, d2 As Integer, h As Integer d1 = date1 - Range("Год2023").Cells(1) + 1 d2 = date2 - Range("Год2023").Cells(1) + 1 For i = d1 To d2 h = h + Range("Год2023").Cells(i).Offset(1, 0) Next WorkingPeriodHours = h End Function |
Если в ячейках, являющихся аргументами функции WorkingPeriodHours, будут указаны не даты или даты в неправильном порядке (первая дата больше второй), функция возвратит сообщение: «Укажите период». Если будут указаны неверные даты, не входящие в диапазон дат, функция возвратит значение ошибки: «#ЗНАЧ!».
Код функции размещается в стандартном модуле.
Для вставки в ячейку функция WorkingPeriodHours будет доступна в категории «Определенные пользователем» мастера функций Excel.