Query to display multiple months based on start/end date

I am looking for the query for a calculated value based on start and end dates to display month, multiple months spanned.
For example....

``````Start date: 01/01/17
End date: 04/15/17
``````

Calculated Value displays: Jan, Feb, Mar, Apr

I've tried this...

``````=IF([Start Date]="","",""&MONTH([Start Date])
``````

but it only displays the first month (in numeric format) - `"1"`

I can live with the numeric format for the month, but would be better for the internal stakeholders if it actually provided the month name.

• ♦ If an answer was helpful could you remove your question from the SO unanswered list, by marking it as answered, tnx Jun 4, 2017 at 7:59

3 Answers

This is what you are looking for:

`=TEXT([Start Date],"mmmm")`

It is good you asked on a Friday (don't tell anyone!)

I got myself a beer (20:30 here in 🌷🌷🌷 Amsterdam)

and played around with my (private) CalcMaster tool

First challenge is year roll-over, November to April should list:

`Nov,Dec,Jan,Feb,Mar,Apr`

So after trying a Formula with Mike's `TEXT()` suggestion I switched to a long text string of months and then some calculations, to extract a substring

Your single Formula 'MonthSpan' is:

=LEFT(RIGHT("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec",LEN("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec")-(MONTH(StartDate)-1)*4),MONTH(EndDate-StartDate)+1*4-1)

Since the string is a fixed length (24 months) = 24 * 4 - 1 comma = 95

``````=LEFT(
RIGHT("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec"
,95 -  (MONTH(StartDate)-1) * 4
)
,MONTH(EndDate-StartDate) + 3
)
``````

How I got there:

You can use the above Formula, this is just to show the steps with multiple Columns I used

Merging multiple columns into a single end-result

• Thank you! I started with your query, and had a few odd issues, but did have some internal help tweaking it for our SP2013 instance. Here's what we ended up with: `code` =LEFT(RIGHT("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,",96-(MONTH(StartDate)-1)*4),(DATEDIF(StartDate,EndDate,"ym")+1)*4-1) May 16, 2017 at 14:01
• Great! Then you can mark the question answered (add your solution to your question as well) and it is documented for future searches. The 96 in your solution instead of my 95 is because you have a , at the end of your month string. May 16, 2017 at 14:16

Query for a calculated value based on start and end dates that will display that displays multiple months spanned.

For example.... Start date: 01/01/17 End date: 04/15/17
Calculated Value displays: Jan, Feb, Mar, Apr

``````=LEFT(RIGHT("Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,",96-(MONTH(StartDate)-1)*4),(DATEDIF(StartDate,EndDate,"ym")+1)*4-1)
``````