0

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

null
    • Sean
    • 4 yrs ago
    • Reported - view

    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.

    • agiorno
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    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!

    • agiorno
    • 4 yrs ago
    • Reported - view

    Thank you very much!

    • agiorno
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    Agiorno, Do you have a pattern or rule you are using to exclude dates?

    • agiorno
    • 4 yrs ago
    • Reported - view

    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.

    • Sean
    • 4 yrs ago
    • Reported - view

    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)

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Is it between "two dates" or "two date arrays"?

    Here is between two dates. 

    betweenDatesInclusive

Login to reply

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 2395Views