Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Split years between two dates

Topic Labels: Formulas
1337 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex_Guillaume
5 - Automation Enthusiast
5 - Automation Enthusiast

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

4 Replies 4
Raminder_Singh
7 - App Architect
7 - App Architect

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. :slightly_smiling_face:
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 :slightly_smiling_face: