Skip to main content

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… 😦


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.

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. 🙂

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)

)

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)

)


Wow Kuovonne !

You are a magician!

Thank you so much 🙂

Reply


Terms & Conditions