Years between

AppSheet AppSheet Q&A
1

I have a text column with this value:
2018 - 2021
I would like to find an expression that can calculate the years between this range.
The output should be an EnumList:
2018, 2019, 2020, 2021

2

List Generator Expressions Feature Requests

NUMBERLIST(0,10) = {0,1,2,3,4,5,6,7,8,9,10} DATELIST(TODAY() , TODAY()+7) = {2/3/21 , 2/4/21,… , 2/10/21} ALPHALIST(“a” , “d”) = {“a” , “b” , “c” , “d”} DECIMALLIST(0,0.5,0.1) = {0,0.1,0.2,0.3,0.4,0.5}

1 Like
3

Set up a Table that is just a list of years.

3X_a_1_a186b95f7e9115597cdc5b3c644ad18777822a56.png
3X_a_1_a186b95f7e9115597cdc5b3c644ad18777822a56.png196×592 6.9 KB

Then pull a matching List from that:

FILTER(
  year ,
  AND(
    [year] <= NUMBER( INDEX( SPLIT( [text] , " - " ) , 2 )
    [year] >= NUMBER( INDEX( SPLIT( [text] , " - " ) , 1 )
  )
)
4 Likes
4

Thank you very much @Marc_Dillon. Instead of using an extra table, could I also hardcode the years list in the expression?

5

I can’t think of any way. Because you need to filter an existing list of dates, by comparing against your start and end. There is no way to do something like this:

FILTER(
  LIST(x,y,z)
  ...
)
3 Likes
6

Like this?

TOP(
  LIST(
    (YEAR([Date]) - 0),
    (YEAR([Date]) - 1),
    (YEAR([Date]) - 2),
    ...,
    (YEAR([Date]) - N),
  ),
  M
)

Where N is the most number of years you’ll allow, M is the number you need now, and M <= N.

4 Likes
7

Thank you @Marc_Dillon and @Steve these both very good solutions.

2 Likes