Creating new lines to keep start/end date in the same year

[brian_mcdonough]

Looking to find a tool or tools that will turn the line below into three lines with the following start and end dates:

10/1/2022 - 12/31/2022

1/1/2023 - 12/31/2023

1/1/2024 - 1/31/2024

[Kakuffo]

The tool would be append fields i believe to fill the gaps but i dont quite understand the logic here, could you provide some additonal information?

[PanPP]

Hi @brian_mcdonough 

I have uploaded a sample WF that extracts the Min and Max date.

Hope this helps. If it does, please like the post and mark it as a solution.

[Kakuffo]

Hey @brian_mcdonough  have attached a solution based on my understanding it would be an append fields tool. I removed the original start and end date parsed out the ones you want to populate each line and appended them to the single line to create three lines for each date range.

[brian_mcdonough]

I don't think this will work because there is a lot more data with various start dates, end dates, discounts, and other differences.

I need the three lines at the top to turn into 9 lines like this, keeping all other information the same

[Kakuffo]

@brian_mcdonough  my solution does exactly this keeps all information constant other then the start and end dates which will be only thing changing. ~I~ removed the original start and end date fields and then appended the new ones in

[brian_mcdonough]

Ok thank you, but will I have to map out the text input for each of these unique start and end date combinations? I have 42 scenarios where the start and end date cross years

[Kakuffo]

If i understand the logic correctly i have simply renamed the fields and appended again here, is this the output you would want?

[nduan]

Hi @brian_mcdonough 

I have created a workflow with a couple of tools that will dynamically generate those rows based on the difference between the Start Date and End Date. I have added a test set of data in the second part of the workflow. 

What this workflow is doing is determining the difference between the Start and End Dates, generating rows for each year of difference regardless of crossover, creating formulas to determine the new Start and End Date, and then cleaning up the intermediary calculations in the Select Tool. 

Attaching the workflow here. Let me know if this works for your use case. If so, please like and mark this solution as correct.