How to get a list of dates between two dates

AppSheet AppSheet Q&A
1

Good morning everyone!

Does anyone know how to get a list of dates between two dates?

For example:

Start date 3/17/2022

End Date: 3/21/2021

I need it to return a LIST with the dates that fit in that range:

3/17/2022, 3/18/2022, 3/19/2022, 3/20/2022, 3/21/2022

is this possible?

2

Not really possible at the moment, @Marc_Dillon posted a feature request that would work, but no movement on it yet. List Generator Expressions - Google Cloud Community

The best you could do is have a list of all possible dates in another table, then you could select values between the two dates from it.

3 Likes
3

Ok graham, mañana me pongo manos a la obra y te estaré preguntando con más detalle en función de lo que vaya haciendo! Gracias por tu valiosa respuesta

4

I would think that it should be possible if the maximum range of dates isn’t too big. In other words, if you knew the starting date and also knew that the range could not be more than 10 days or so, you could make a rather long expression that would list all 10 days and then determine which ones fit in the range.

3 Likes
5

Para el ejemplo he usado un rango pequeño, sin embargo esto no necesariamente sería así. Me parece entonces que Appsheet debería buscar una forma de automatizar esto.

1 Like
6

I see. Yes. Some kind of function would be nice. If you already have a list of dates, I can think of ways to eliminate any dates that fall outside of the range. However, I can’t think of a simple way to do what Marc’s functions would do – make up a list of consecutive dates.

7

Only one possible solution which came up with my mind is to twist the tips and tricks of this.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/FAQ-Add-rows-for-a-date-range/m-p/321864

This trick is add new rows based on date range. However, you can add dates value to ENUMLIST type column by looping actions.

@takuya_miyai

5 Likes
8

Cool! I had forgotten about this specific post but the idea of using some kind of loop, as @Steve has taught us, occurred to me too. By the way, I miss Steve. I hope to see him around again.

2 Likes
9

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Hello-I-have-a-problem-I-should-manage-the-a/m-p/324311

This could help

10

I managed to build a way to get the output you are asking for.
In my use case, it is to capture the dates between my colleagues leave dates.
In the leave application form, users select Leave Start Date (LSD) and Leave End Date (LED).
Thereafter, I maintain a seperate table of the different days in the year from 1/1/YYYY to 12/31/YYYY.

I use the INTERSECT() function coupled with two SELECT() functions to achieve the desired output:
INTERSECT(
SELECT(DatesInYear[Dates], [Dates] >= [LSD]),
SELECT(DatesInYear[Dates], [Dates] <= [LED])
)

3 Likes