2020년 1월 6일 내용 추가: 날짜 형식 통일
배경 설명
Access에서 날짜를 산정하는 방법은 Datediff() 함수를 사용하는 것이다. 하지만 Datediff() 함수는 주말과 공휴일을 고려 못한다. 업무 평가 등의 보고서를 작성할 때 주말과 공휴일은 빼고 날짜를 계산해야 할 경우가 있는데, 이럴 때는 VBA를 사용해 별도 함수를 짜는 수밖에 없다.
완성 코드
Public Function fWorkingDays(dteStartDate As Date, dteEndDate As Date, Optional WeekendDays As String = "1,7") As Integer
Dim intCount As Integer
Dim wkdays As String
intCount = 0
wkdays = "1234567"
wkdays = Replace(wkdays, Left(WeekendDays, 1), "")
wkdays = Replace(wkdays, Right(WeekendDays, 1), "")
Do While dteStartDate <= dteEndDate
If InStr(wkdays, Weekday(dteStartDate)) = 0 Then
Else
If DCount("*", "tHoliday", "[HolidayDate] = # " & Format(dteStartDate, "mm\/dd\/yyyy") & " # ") > 0 Then
Else
intCount = intCount + 1
End If
End If
dteStartDate = dteStartDate + 1
Loop
fWorkingDays = intCount
End Function간략한 과정
- [tHoliday] 테이블을 만들고 [HolidayDate] 필드를 생성한다. 양식은 'Date/Time'
- [tHoliday] 테이블에 공휴일을 모두 입력한다.
- 함수를 사용한다.
함수 해부
함수 이름은 [fworkingdays]로, 사용자 입력 변수는 3가지. [dteStartDate], [dteEndDate], [WeekendDays]로 한다.
Public Function fWorkingDays(dteStartDate As Date, dteEndDate As Date, Optional WeekendDays As String = "1,7") As Integer함수의 핵심은 사용자가 입력한 시작일과 종료일 사이에서 하루씩 Loop하는 것이다. Loop를 할 때마다 해당 날을 헤아려야 하면 헤아리고, 휴일 등이라서 헤아리면 안 되는 날이면 제외한다. 따라서 헤아린 날을 받아줄 변수 [intCount] 필요하다. 그리고 요일을 숫자로 나타내는 [wkdays] 변수를 선언하고 일주일을 정해준다. "1234567"을 지정하면 된다.
Dim intCount As Integer
Dim wkdays As String
intCount = 0
wkdays = "1234567"[wkdays] 변수에서 사용자가 입력한 주말에 해당하는 날짜를 제거한다. 이렇게 함으로써 [wkdays] 변수에는 '주일'만 남는다.
wkdays = Replace(wkdays, Left(WeekendDays, 1), "")
wkdays = Replace(wkdays, Right(WeekendDays, 1), "")반복을 시작한다. 조건은 시작일이 종료일과 같아질 때까지.
Do while dteStartDate <= dteEndDateIf 조건을 줄 건데 두 가지 if에 해당하지 않을 때만 intCount에 +1 해줄 것이다. 1. 주말이 아닐 때, 2. 법정 공휴일이 아닐 때. 먼저 주말이 아닐 때부터 보자. 위에서 [wkdays] 변수에서 주말을 빼놓았으므로 InStr 함수에 넣어서 돌리면 된다.
If InStr(wkdays, Weekday(dteStartDate)) = 0 Then
Else그 다음에는 법정 공휴일이 아닐 때. 이건 Dcount를 사용한다. [dteStartDate]를 [tHoliday] 테이블에서 DCount 돌렸을 때 값이 1이상 나오면 공휴일이라는 소리다. 그리고 마지막 Else문에 위의 두 조건에 해당하지 않을 때만 intCount+1을 하는 명령어를 넣는다. 여기서 주의할 점은 Format 함수를 사용한 부분이다. VBA는 무조건 미국 날짜 양식을 따르고 (mm/dd/yyyy) 현재 내가 살고 있는 아일랜드는 유럽 날짜 양식을 (dd/mm/yyyy) 따르는데, 이걸 통일하지 않으면 2020년 6월 1일을 공휴일 테이블에서 휴일로 지정해 두어도, 2020년 6월 1일이 아니고 1월 6일이 휴일이라고 함수가 판단한다.
If DCount("*", "tHoliday", "[HolidayDate] = # " & Format(dteStartDate, "mm\/dd\/yyyy") & " # ") > 0 Then
Else
intCount = intCount + 1
End If
End IfIf 문이 끝나고 Loop를 닫기 전에 dteStartDate를 +1 해준다.
dteStartDate = dteStartDate + 1
Loop마지막으로 최종 [IntCount] 값을 fWorkingDays 함수 결과로 지정한다.
fWorkingDays = intCount테스트
아무데서나 fWorkingDays() 함수를 소환해도 되고, Debug.Print 기능을 이용하려면 아래 코드를 실행하면 된다. 쿼리나 보고서 내에서도 먹힌다.
Sub testfWorkingDays()
Debug.Print "Number of Working days --> " & fWorkingDays(#10/18/2019#, #10/28/2019#, "1,7")
End Sub