Return Months from Start/End Dates

See edited formula in the reply below…

You could start with this formula, which will work for covering up to 3 years (i.e. 2018, 2019, 2020). Adding additional years would require additional levels to the formula :thumbs_up:

I find it interesting to see the many different ways there are to accomplish things with code.

Would you mind explaining about how your formula works so that it would be easier to use your formula as a starting point?

It looks like your method involves starts with strings containing month abbreviations and then does a lot of string trimming to figure out which months to keep and string substitutions to insert the year.

In cases like this it would be nice if Airtable formulas allowed embedded comments to explain how a formula works.

Sure! Sorry for not providing some more context last night – I had to get to bed :yawning_face:

First, an update to the formula above to fix a small bug and add some more #ERROR checking:

IF(OR({Start Date}=BLANK(), {End Date}=BLANK()), '🔥Missing one or more dates!', IF(
	YEAR({End Date})-YEAR({Start Date})>2,
	'🔥Date range is too large!',
IF(
	IS_AFTER({Start Date}, {End Date}), 
	'🔥Start date is after end date!', 
SUBSTITUTE(DATETIME_FORMAT({Start Date}, 'MMM')&' '&DATETIME_FORMAT({Start Date}, 'YY')&
IF(
	OR(DATETIME_DIFF({End Date}, {Start Date}, 'months')>1, ABS(MONTH({End Date})-MONTH({Start Date}))>1),
	', '&SUBSTITUTE(MID('Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec', FIND(DATETIME_FORMAT({Start Date}, 'MMM'), 'Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec')+5, 3+5*(DATETIME_DIFF({End Date}, {Start Date}, 'months')-1)), ', ', ' '&DATETIME_FORMAT({Start Date}, 'YY')&', ')&IF(MONTH({Start Date})!=12, ' '&DATETIME_FORMAT({Start Date}, 'YY')))&
IF(
	YEAR({End Date})-YEAR({Start Date})>1,
	', '&SUBSTITUTE('Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec', ', ', ' '&DATETIME_FORMAT(DATEADD({Start Date}, 1, 'year'), 'YY')&', ')&' '&DATETIME_FORMAT(DATEADD({Start Date}, 1, 'year'), 'YY'))&
IF(
	AND(
		MONTH({Start Date})!=MONTH({End Date}),
		YEAR({Start Date})=YEAR({End Date})), 
	', '&DATETIME_FORMAT({End Date}, 'MMM')&' '&DATETIME_FORMAT({End Date}, 'YY'), 
	IF(
		YEAR({Start Date})!=YEAR({End Date}),
		', '&SUBSTITUTE(LEFT('Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec', FIND(DATETIME_FORMAT({End Date}, 'MMM'), 'Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec')+2), ', ', ' '&DATETIME_FORMAT({End Date}, 'YY')&', ')&' '&DATETIME_FORMAT({End Date}, 'YY'))), ', , ', ', '))))

The first three IF() statements check for errors: if any dates are missing, if the date range covers more than 3 years, or if the start date is after the end date.

After that, the SUBSTITUTE() function removes any doubled-up commas (, ,), while the following concatenated DATETIME_FORMAT() functions will give the month and year of the start date.

Then, things get a little tricky. The next IF() statement uses the methods you describe (string trimming a pre-specified string of months in a year, based on the month of the starting date AND the number of months between the start and end dates. Since all month abbreviations are three characters, this allows us to calculate the exact number of characters to trim) to insert the following months for that current year. An additional SUBSTITUTE() function swaps out the commas in the list of months with the two-digit year (of the starting year), followed by a comma.

The next IF() triggers if there is a third, middle year in between the start and end years – using the string trimming and substitution methods above to insert a string of months (from Jan - Dec) with the middle year included. Expanding this formula to cover more years would require duplicating this section and adjusting the DATEADD() formula to add 2, 3, 4… years. You would also need to adjust some of the YEAR()-based qualifiers for other IF() statements in the function (the second IF() statement, for example).

The next IF() statement is a nested IF(), which takes care of the month/year for the end date. This can run into some issues if the ending year is different from the start – namely, the months from January to the end month won’t be included. The nested IF() will include those, up until the final month :thumbs_up:

Thanks for the detailed explanation. It sounds like to extend your formula beyond three years, one only needs to duplicate the middle IF statement and add some logic to count the number of years?

By the way, I was experimenting with your formula (as is) and when I enter 5/20/2019 as the start date, and 7/5/2019 as the end date, I get the result of May 19, Jul 19 without June.

I totally get having to go to bed. I was also coding late last night on my phone and ended up with a very embarrassing syntax error (missing comma) that prevented my code from running at all. I have since fixed the syntax error in my original post and added the formatting the original poster requested.

See edited formula in the reply below…

You could start with this formula, which will work for covering up to 3 years (i.e. 2018, 2019, 2020). Adding additional years would require additional levels to the formula :thumbs_up:

Thanks for the detailed explanation. It sounds like to extend your formula beyond three years, one only needs to duplicate the middle IF statement and add some logic to count the number of years?

By the way, I was experimenting with your formula (as is) and when I enter 5/20/2019 as the start date, and 7/5/2019 as the end date, I get the result of May 19, Jul 19 without June.

I totally get having to go to bed. I was also coding late last night on my phone and ended up with a very embarrassing syntax error (missing comma) that prevented my code from running at all. I have since fixed the syntax error in my original post and added the formatting the original poster requested.

I think I fixed that last error you found – thanks so much for testing it out so well! Thought I had all of the angles covered, but there were some sneaky instances I didn’t think of which threw wrenches in the works.

Fingers crossed – the formula now should do it.

This can be done with formulas. However, you would need to combine multiple functions in the formula.

Here is one approach to get you started.

DATETIME_FORMAT( {Start Date}, "MMM YY" )
&
IF(
  IS_BEFORE(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
      1, 
      "months"
    ),
    {End Date}
  ),
  ", " &
  DATETIME_FORMAT(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
        1, 
        "months"
    ),
    "MMM YY"
  )
)
&
IF(
  IS_BEFORE(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
      2, 
      "months"
    ),
    {End Date}
  ),
  ", " &
  DATETIME_FORMAT(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
        2, 
        "months"
    ),
    "MMM YY"
  )
)
&
IF(
  IS_BEFORE(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
      3, 
      "months"
    ),
    {End Date}
  ),
  ", " &
  DATETIME_FORMAT(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
        3, 
        "months"
    ),
    "MMM YY"
  )
)
&
IF(
  IS_BEFORE(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
      4, 
      "months"
    ),
    {End Date}
  ),
  ", " &
  DATETIME_FORMAT(
    DATEADD(
      DATETIME_PARSE("1/" & MONTH({Start Date}) & "/" & YEAR({Start Date}), "D/M/Y"), 
        4, 
        "months"
    ),
    "MMM YY"
  )
)

You would need to extend the pattern for the maximum number of months.

The all of the formulas used are documented in the formula field reference.

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