Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 01, 2021 11:53 AM
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
Mar 03, 2021 04:28 AM
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.
Mar 03, 2021 05:41 AM
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.
Mar 03, 2021 08:19 AM
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)
)
Mar 03, 2021 08:50 AM
Wow Kuovonne !
You are a magician!
Thank you so much :slightly_smiling_face: