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?
There is not a formula function explicitly for what you’re trying to achieve, but I’m sure there’s a way to do it by combining various function together. It’d be a bit of work, so you might want to checkout the work offered section if no one posts a solution here.
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.
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 
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 
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
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.
Good catch! I think I fixed it – There was a DATETIME_DIFF() function in there which wasn’t calculating how I thought it would and was throwing that off.
Thank you very much for typing this all out! I was able to paste this into my airtable sheet and it works like a charm.
And thank you to kuovonne for testing it out!
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.
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?
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
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'
)