1

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 – Danny '365CSI' Engelman Jun 4 '17 at 7:59
1

This is what you are looking for:

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

0

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) – K-Thor May 16 '17 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. – Danny '365CSI' Engelman May 16 '17 at 14:16
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.