Formulas - how to return all days between two dates

How to show consecutive days in a control (eg, a drop down)

The formula beneath shows the basic syntax that we would use. This formula produces an output table that contains all dates between the "1st January 2021" and "6th January 2021" inclusively.

With({startDate:DateValue("2021-01-01"),
      sequenceDays: Sequence(DateDiff(DateValue("2021-01-01"),
                                      DateValue("2021-01-06")) + 1)
      },
      AddColumns(sequenceDays,
                 "Date",
                 DateAdd(startDate, Value - 1, Days)
       )
)

To apply this formula, we would replace the start date and end date values inside the With block, with our target start and end dates..

This formula relies on a single column table called sequenceDays. This table contains a consecutive sequence of numbers between the start and end dates. In this example, the content of the sequenceDays collection would contain the following data:

[1,2,3,4,5,6]

We use a combination of the Sequence and DateDiff functions to generate the sequenceDays collection. In this example, DateDiff returns the number of days between the start and end date which is 5. To produce an output that is inclusive of both the start and end dates, we need to add 1 to this value.

We add a column called "Date", which we calculate by adding the sequence value to the start date. Because the sequence begins with 1 and we want to add zero days for the first output record, we subtract the 'value' in the sequence by 1.

The screenshot beneath illustrates this technique, as applied to the Items property of a drop down control.

How to populate a collection of consecutive days with additional fields

As a logical extension of the above, here's the formula to populate a collection with the same days, but with extra columns to display the day numbers.

With({startDate:DateValue("2021-01-01"),
      sequenceDays: Sequence(DateDiff(DateValue("2021-01-01"),
                                      DateValue("2021-01-06")) + 1)
      },
      ClearCollect(colDates, 
                   AddColumns(sequenceDays,
                              "Date",
                               DateAdd(startDate, Value - 1, Days),
                              "Day Num",
                              "Day " & (Value),
                              "Day Name",                              
                              Text(DateAdd(startDate, Value - 1, Days),
                                   "dddd"
                              )
                   )
      )
)

Conclusion

A common requirement is to build a collection of sequential days between the start and end date value. This post highlighted how to accomplish this with the help of the Sequence, DateDiff, and AddColumn functions.