List Months Based on Start/ End date

[r24igh]

Hi all,

I'm trying to create a list of months based on two dates, start and end date. I can't for the life of me work out how to do it!

The information the formula would use would be the dates from when a project begins and when a project ends. This would list the months including and between the two dates.

Any help would be greatly appreciated.

 

[Isskint]

You would probably need to create a custom function for this or you could do it with a query and nested iif() statements.

Custom function would be simplest. The below is a suggestion, but untested.

Public Function ActiveMonths(StartDate As Date, EndDate As Date) As String

Dim ThisMonth As Date

ThisMonth = StartDate 'set counter date to startdate

If DateDiff("m", StartDate, EndDate) >= 12 Then' if date range is 12 or more months then every month
    ActiveMonths = "January, February, March, April, May, June, July, August, September, October, November, December"
Else
    While Month(ThisMonth) <> Month(EndDate)'loop through date range until end date month reached
        ActiveMonths = ActiveMonths & ", " & MonthName(Month(ThisMonth))
        ThisMonth = DateAdd("m", 1, ThisMonth)
    Wend
End If

ActiveMonths = Right(ActiveMonths, Len(ActiveMonths) - 2)'remove leading ", "

End Function

 

[informer]

Hi r24igh

A very simple way to obtain your list, create a table month with this structure:
monthNumber as integer
monthName as string

Data
1 | january
2 | february
3 | april
and so on

then a SQL query as follows:

SELECT month.*
FROM [month]
WHERE (((month.[monthNumber]) Between Month(#1/1/2016#) And Month(#2/1/2016#)));