Split years between two dates

Hi !
I got a beginning date : 01/01/2010
I got an ending date : 31/12/2014

In a field, I would like to show : 2010, 2011, 2012, 2013 (the dates included between beginning and end)

Any clue on the formula I should use ???
I’ve investigate for hours with no answer… :frowning:

A big thank you in advance

Alex

Hi @Alex_Guillaume, this might be hard to do using only formulas. A script might be a better fit here. Are you on the pro plan because scripts are only available in the pro plan.

Yes Raminder, I have the pro plan. :slight_smile:
And actually, I made a mistake in the description.
The results would be : 2010, 2011, 2012, 2013 and 2014. If the period includes at least one day in a specific year, then the year would appear in the result.

Do you have a maximum number of possible years in the interval?
If so, you can repeat this pattern for the maximum number of years.

YEAR({Start Date})
& IF(
  YEAR({End Date}) >= YEAR({Start Date}) + 1,
  ", " & (YEAR({Start Date}) + 1)
)
& IF(
  YEAR({End Date}) >= YEAR({Start Date}) + 2,
  ", " & (YEAR({Start Date}) + 2)
)
& IF(
  YEAR({End Date}) >= YEAR({Start Date}) + 3,
  ", " & (YEAR({Start Date}) + 3)
)
& IF(
  YEAR({End Date}) >= YEAR({Start Date}) + 4,
  ", " & (YEAR({Start Date}) + 4)
)
& IF(
  YEAR({End Date}) >= YEAR({Start Date}) + 5,
  ", " & (YEAR({Start Date}) + 5)
)

image

Wow Kuovonne !
You are a magician!
Thank you so much :slight_smile:

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.