I am trying to calculate the period between two dates and return the result in months and days. I want to include specific months of the year not all the months. The included months are from September to March.
Here's my try but the output not correct
Code: Select all
Function MonthDayDiff(start_date As Date, end_date As Date) As String
Dim months As Long, days As Long
Dim start_month As Long, end_month As Long
Dim i As Long, included_months As Variant
included_months = Array(9, 10, 11, 12, 1, 2, 3)
start_month = Month(start_date)
end_month = Month(end_date)
If start_month > end_month Then
months = end_month + 12 - start_month
Else
months = end_month - start_month
End If
If IsError(Application.Match(end_month, included_months, 0)) Then
months = months - 1
days = Day(DateSerial(Year(end_date), end_month, 0)) - Day(end_date) + 1
ElseIf IsError(Application.Match(start_month, included_months, 0)) Then
months = months - 1
days = Day(start_date)
Else
days = Day(end_date) - Day(start_date) + 1
End If
MonthDayDiff = months & " months, " & days & " days"
End Function
January is included. February is the same. March is the same. September is included too. The two days in October are included. so the expected output would be [4 months & 2 days]