Create a list of date in EnumList type column given Start Date and End Date

AppSheet AppSheet Q&A
1

Hi, everyone.

Let say I have 3 columns, [start_date], [end_date] and [list_of_date], where [start_date] and [end_date] are Date type columns and [list_of_date] is EnumList type column.

Is there any workaround to achieve something like if I fill in [start_date] = 2/9/2024, [end_date] = 2/12/2024, then the [list_of_date] will create a list of date 2/9/2024,2/10/2024,2/11/2024,2/12/2024.

Thanks for your help.

Really appreciate it.

2

I used the following formula and it worked but only limited to certain date.

LIST(
[start_date],
IF([start_date] < [end_date], [start_date] + 1, “”),
IF([start_date] + 1 < [end_date], [start_date] + 2, “”),
IF([start_date] + 2 < [end_date], [start_date] + 3, “”),
IF([start_date] + 3 < [end_date], [start_date] + 4, “”),
IF([start_date] + 4 < [end_date], [start_date] + 5, “”),
IF([start_date] + 5 < [end_date], [start_date] + 6, “”),
IF([start_date] + 6 < [end_date], [start_date] + 7, “”),
IF([start_date] + 7 < [end_date], [start_date] + 8, “”),
IF([start_date] + 8 < [end_date], [start_date] + 9, “”),
IF([start_date] + 9 < [end_date], [start_date] + 10, “”),
IF([start_date] + 10 < [end_date], [start_date] + 11, “”),
IF([start_date] + 11 < [end_date], [start_date] + 12, “”),
IF([start_date] + 12 < [end_date], [start_date] + 13, “”),
IF([start_date] + 13 < [end_date], [start_date] + 14, “”)
)

3

It looks like AppSheet doesn’t really have the required functions for generating a sequence between two values in the formulas at least.

You could do it using another table that includes a list of the dates (similar to what is mentioned here). If you want to make this more dynamic (so you wouldn’t need to manage that table,) you could use a Google Sheet as a data source with a table that lists a large set of dates based on a formula. (For example, something like this

=ArrayFormula(Today()+Row(A2:A)-60))

Will start at the date 60 days prior to today and list all the dates until it hits the last row in your sheet (thus moving every day so you never have to go and update the formula)).

Then you would be able to “Select” from that list the values that fall between your start and end dates, and present those as your list of dates.

Alternatively, if you don’t actually need the list of dates. You could use the valid if section to constrain the users selections based on the start and end dates.

If you’re trying to get someone to choose a date between your start and end. Make a field of the type “Date”

TheWorkFlow_0-1707498906295.png
TheWorkFlow_0-1707498906295.png753Ă—274 15.3 KB

The change the valid if to reference whether [_this] is between those dates and include an error message to communicate this accurately.

TheWorkFlow_1-1707498955587.png
TheWorkFlow_1-1707498955587.png790Ă—318 22.4 KB

valid if:

and([_this]>=[Start Date],[_this]<=[End Date])

Invalid error:

"You must select a date between "&[Start Date]&" and "&[End Date]

TheWorkFlow_2-1707499123411.png
TheWorkFlow_2-1707499123411.png610Ă—141 5.92 KB

The downside of this is, the date picker doesn’t actually block out the dates that are invalid. It waits to see what the user selects and then checks the validation when they try to progress. But it’s a slightly more built in solution if it fits your need then having to link in another table to reference the list of dates.

4

If AppSheet have the ability to loop the formula, then it should be in the blink of eyes to achieve that. Thanks for the workaround. I will try.

5

Agreed! Some limited looping function would help with questions like this.