Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

List dates between two dates

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

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    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))

     

     

4 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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))

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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