Find Month

This webpage demonstrates how to use and create a function for finding if a month is between two dates.  Finding a month would be easy if the month of the start date was earlier than the month of the end date.  But sometimes if you have a semi-annual model or a quarterly model the start of the month could be something like October while the end of the month could be something like March.  Then if the month is February, that month is between the start date and the end date.  I am sure you can do this with a long formula or several formulas.  But I run into this quite often and I have made a UDF to solve it.  The UDF produces a TRUE or FALSE flag that is illustrated in the screenshot below.

 

 

You can copy the UDF into your model using the code below.

 

.

 

Function find_month(Start_Date, End_Date, Test_Month)

find_month = False

If Month(Start_Date) <= Month(Start_Date) Then

If Month(Start_Date) <= Test_Month And Month(End_Date) >= Test_Month Then

find_month = True

End If

End If


If Month(Start_Date) >= 6 And Month(Start_Date) > Month(End_Date) Then

' Example of feb 2 and Nov (11) to April (4)

If Test_Month <= 6 And Test_Month <= Month(End_Date) Then

find_month = True

End If

If Test_Month >= 6 And Test_Month >= Month(Start_Date) Then

find_month = True

End If

End If


End Function