Need to fill in missing date periods

[AkisM]

My input data looks like this:

Date From	Date To
2020-08-14

And I am trying to write something that would fill in the missing columns, to the end of each monthly period, so my target would look like this:

2020-08-14	2020-08-31
2020-09-01	2020-09-30
2020-10-01	2020-10-31
2020-11-01	2020-11-30
2020-12-01	2020-12-31
2020-01-01	2021-01-31
2020-02-01	2021-02-28
2020-03-01	2021-03-31
2020-04-01	2021-04-31

Any ideas?

[Jonathan-Sherman · Accepted solution]

Hi @AkisM,

I'd go with a formula to add another month (with datetimeadd() function, trim it to be just the first of the month (with datetimetrim() function) and finally another datetimeadd() to subtract one day. Therefore getting us to the last day of the month!

DateTimeAdd(DateTimeTrim(DateTimeAdd([Date From],1,'month'),'firstofmonth'),-1,'day')

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

[AkisM]

Hi @Jonathan-Sherman . Thanks, that works great for finding the last day of the month, but it assumes that we always have filled in the "Date From" field. In my example we don't (we have it only in the first row). So I imagine that we also need some kind of multi row formula to populate the fields downwards but I'm not sure what

EDIT: Fixed it myself with a couple of multi-row formulas and then used your formula to fill in the last days from each month. Thanks!

[Jonathan-Sherman]

That's great news @AkisM, nice work! Who doesn't love a good multi-row formula!

[suby]

Hello,

Could you please share your workflow on how you did it.

Thanks

[AkisM]

Here you go @suby