List dates between two dates

[Anonymous]

Hi all,

I'm looking for a formula to list dates between 2 dates. Suppose I have the following example:

Column A) InitialDate: 19/07/2019

Column B) EndDate: 19/10/2019

so, I would like to get as result.....19/07/19, 19/08/19, 19/09/19 and 19/10/19 (one occurrence per month dd/mm/yy)

I'm using the formula: {Number.From([InitiaDate])..Number.From([EndDate)]}  but I getting as results all dates , such us 19/07/2019, 20/07/2019, 21/07/2019....till 19/10/2019. HowCould I solve this issue ?

Thanks in advance 

Jeferson

[Anonymous]

Hi Imke,

It worked ! Thanks vey much !

I just did a small adjustment as follow:

= List.Transform({0.."number of events displayed at the sheet -1"}, (x) => Date.AddMonths([FROM], x))

Regards,

Inacio11

[Anonymous]

Hi,

I thankful with the early repply, however I'm still facing errors. Please check the screeshots for more details.

Screen1 

Screen2 

Thanks in advance

Inacio11

[ImkeF · Accepted solution]

Hi @Anonymous 

you don't use it as a function. Then the "each" isn't ambigous any more.

Try this for result instead:

Result = List.Transform({0..NoOfIntervals}, (x) => Date.AddMonths([FROM], x))

[ImkeF]

Hi @Anonymous 

this function should get you there:

(From as date, To as date) =>
let
    NoOfIntervals = (Date.Year(To)*12+Date.Month(To))-(Date.Year(From)*12+Date.Month(From)),
    Result = List.Transform({0..NoOfIntervals}, each Date.AddMonths(From, _))
in
    Result