How can I get the sequence between dates on glideapps? I tried with JavaScript code snippet and Excel formulas but did not work.
Could you please help me?
Best,
Xavier
What is the sequence between dates? A list of all the dates between?
Exactly, I just need the sequence of dates between Initial and last value.
12/10/2023
13/10/2023
14/10/2023…… and so on
Do you need the dates on their own row?
Not necessary, could be on another row
I mean, do you need
Date 1
Date 2
Date 3
Or can it be
Date 1, Date 2, Date 3 … ?
Could it be Date 1, Date 2… etc
Any ideas?
May we know what’s your use case for this? Are you using it for creating relations down the road?
1 Like
Using JavaScript:
const startDate = new Date(p1);
const endDate = new Date(p2);
const dates = [];
let currentDate = new Date(startDate);
while (currentDate <= endDate) {
dates.push(new Date(currentDate));
currentDate.setDate(currentDate.getDate() + 1);
}
return dates.join(',');
But I suspect that’s probably not what you want.
As @ThinhDinh mentioned, it would be useful if you told us how you plan to use this. We might then be able to suggest an appropriate solution for your use case.
1 Like
I am trying to get the sequence between Initial and Last day because those days are the ones the business pays because it does daily, What I am triying to figure out is the day the business did not pay by making a function and create a ListRelation from the day in charge and the sequence.
For example
Initial payment: 20/10/2023
Last payment: 30/10/2023
the sequence should be: 20/10/2023, 21/10/2023, 22/10/2023…30/10/2023
DatePayment1: 20/10/2023
DatePayment1: 21/10/2023
DatePayment1: DID NOT PAY
I want to build a list relationship and filtering them by days appeared in the sequence and if not, it should give me a count of the days that didn’t pay. I know how to do that, I just want to figure out the sequence from initial and Last dates.
Does that make sense?
Okay.
If your only goal is to get a count of days where payment wasn’t made, then all you need to do is use a math column to count the number of days between the Start Date and the End Date (eg. Trunc(End-Start)), and then compare that to the number of payments made.
If you want something that can be displayed as a List - eg. one item for each payment date - then my recommendation would be to use a Helper Table to dynamically generate the list. Essentially you’d set the Start and End Dates in two user specific columns, apply those to all rows with Single Value columns, use a Row Index plus Math column (Date + Index) to generate a list of Dates between the two, and then use that to build a relation back to your Payments table. Any row where the relation is empty would represent a missed payment date.
You would just need to ensure you have enough rows in the Helper table to cover the maximum payment period.