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?

[Zollie · Accepted solution]

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.

[kuovonne · Accepted solution]

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.

[Nathan_Anderso1 · Accepted solution]

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:

[kuovonne · Accepted solution]

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

Nathan_Anderson:

You could start with this formula,

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.

[Nathan_Anderso1 · Accepted solution]

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:

[kuovonne · Accepted solution]

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.

Nathan_Anderson:

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

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.

[Nathan_Anderso1 · Accepted solution]

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.

[Alysa_Giustino · Accepted solution]

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!

[Nathan_Anderso1 · Accepted solution]

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.