Split record with start and end date and duration in to multiple records

[msmt85]

Hi all,

Am not sure how to work out the next example but assume need to do something with the generate rows function.

Example table with 1 record:

Start Date - End Date - Duration in days

1.  2019-04-19 - 2019-05-13 - 25 days

Expected table where record 1 is being split in to 2 months with duration for each month:

Month - Duration

1. 2019-04-1 - 12

2. 2019-05-1 - 13

How to achieve this with minimum logic? Suggestions / solutions are more then welcome.

[JoshuaGostick · Accepted solution]

Hi @msmt85,

I've built a workflow that should hopefully help with your issue. What I did was use the Generate Row tool to generate the days from the start date to the end date. Then, I used the Summarize tool to count the number of days in each month.

Let me know if you have any questions.

Thanks,

Josh

[msmt85]

Hi Joshua,

Thanks for your reply, this will indeed do the trick for me.

Is there also another workaround to do it with less row creation? Afraid that with larger datasets this might be an issue.

Cheers!

[JoshuaGostick]

Hi @msmt85,

That's a very valid point. I've played around with the workflow and changed it so that you won't have this issue.

How it works now is that it will only generate rows for the number of months between the start and end date. It therefore won't increase the number of records any more than your desired output. It then uses some logic to calculate the duration in days e.g. if the start month is the same as the start date but not the same as the end month, calculate days from start date to the end of that month.

Hope this works for you!

Thanks,

Josh