Return Months from Start/End Dates

[Alysa_Giustino]

I have a list of tasks in my table and a date range for each task. The date ranges are entered into two different columns, Start Date and End Date. Each of those are formatted as dates. I’d like another column to list all the months that are included in that ranges of dates.

So for example, if Start Date is Jan 1, 2020 and End Date is April 30, 2020, I’d like the Months column to list Jan 20, Feb 20, March 20, April 20.

Is there a formula for this?

[Alysa_Giustino · Accepted solution]

Hi Nathan - I’ve noticed that when a date range spans 3 or more months, the last month in the list is duplicated. For example when the Start is 8/1/20 and the End is 10/30/20, the output is “Aug 20, Sep 20, Oct 20, Oct 20”. Any chance you’d be able to update the formula so that last month in the list doesn’t duplicate?

[Elodie_Delneuf · Accepted solution]

Hello Kuovonne,

I used the RANGE formula you provided above, amazing one!

From this column, I get the MOIS_DEBUT_MISSION (= First month of the range)
Using: LEFT(RANGE_DATES,6)

And the MOIS_FIN_MISSION (= Last month of the range)
Using: RIGHT(RANGE_DATES,6)

The only issue is, the month name abbreviations appear is English - I want them to appear in French

Jan > Jan
Feb > Fev
March > Mars
Apr > Avr
May > Mai
June > Juin
July > Juil
Aug > Août
Sep > Sep
Oct > Oct
Nov > Nov
Dec > Dec

I tried to use the SWITH formula I previously used to convert the number of a month into the name abbreviation of the month:

SWITCH(MONTH(MOIS_DEBUT_MISSION),

01, “Janv.”,
02, “Fév.”,
03, “Mars”,
4, “Avr.”,
5, “Mai”,
6, “Juin”,
7, “Juil.”,
8, “Août”,
9, “Sept.”,
10, “Oct.”,
11, “Nov.”,
12, “Dec.”
)

But that does not work and I coud not find the right way around.

Could you please help me with that?

Thank you!
Elodie

[kuovonne · Accepted solution]

Elodie_Delneuf:

the month name abbreviations appear is English - I want them to appear in French

You can use SET_LOCALE() inside DATETIME_FORMAT() to get French names. See the Formula Field Reference for more details. You will also need to find a French locale from the list of locales.

DATETIME_FORMAT(
    SET_LOCALE({date}, 'fr'), 
    'MMM YY'
)