How to show all dates between two dates?
Hi everyone!
I really can’t find the solution without creating additional table.
Example:
let myArray := [today(), (today+2)]=> 23.04.19, 25.04.19
but what about 24.04.19 which actually I need?
Help please)
9 replies
-
agiorno, Here are a couple of options...
Option 1
let dateArray := [today()];
for i from 1 to 3 do
dateArray := unique(dateArray, today() + i)
end;
format(item(dateArray, 0), "DD/MM/YYYY") + ", " + format(item(dateArray, 1), "DD/MM/YYYY") + ", " + format(item(dateArray, 2), "DD/MM/YYYY")
Option 2
let numToday := number(today());
let oneDay := 86400000;
let numDates := range(numToday, numToday + oneDay * 3, oneDay);
format(date(item(numDates, 0)), "DD/MM/YYYY") + ", " + format(date(item(numDates, 1)), "DD/MM/YYYY") + ", " + format(date(item(numDates, 2)), "DD/MM/YYYY")
If you paste the code in two separate Formula fields you will see the output is the same.
-
Sean, thank you! It’s exactly what I want. Works great!
May you explain logic of this part:
dateArray := unique(dateArray, today() + i)
I want better understand how use it in another case.
thank you again
-
Agiorno, you're welcome. The unique() function allows you to combine arrays and add items to arrays as long as they are unique. The function will remove duplicate items. In the line you are asking about the function is adding the iterative value of "i" (1 & 2 because the "To" value is not inclusive) to today(). It is equivalent to today() + 1 and today() + 2. Hope this helps!
-
Thank you very much!
-
Sean, I find myself in another issue which related with previous one.
Now I have two arrays. First contains all dates in period, Second contains dates that I want exclude.
concat(firstArray) => 24.04.19, 25.04.19, 26.04,19, 27.04.19, 28.04.19;
concat(secondArray) => 24.04.19, 26.04.19;
May be you know how to create thirdArray from previous like:
concat(thirdArray) => 25.04.19, 27.04.19, 28.04.19;
thnx
-
Agiorno, Do you have a pattern or rule you are using to exclude dates?
-
Sean, yes I have. When I create record I always fill date field. I want to calculate an array of dates in which I have not any record. Usually date != today.
-
Agiorno, something like the following is the only thing I can work out...
let dateArray := [today()];
for i from 1 to 5 do
dateArray := unique(dateArray, today() + i)
end;
let firstArray := [item(dateArray, 1), item(dateArray, 3), item(dateArray, 1)];
let secondArray := [item(dateArray, 0), item(dateArray, 2)];
concat(firstArray);
concat(secondArray)
-
Is it between "two dates" or "two date arrays"?
Here is between two dates.
Content aside
- 5 yrs agoLast active
- 9Replies
- 2411Views