Consolidating dates

[hcao]

Hi, I have 4 fields: Progression date, progression end date, promotion date, and promotion end date. I want to use a formula to consolidate these dates to get [Effective date] and [Effective end date], so that it accounts for both progression and promotion dates.

Essentially the logic is for the first effective date/end date, take the earliest of either progression or promotion date (and end date). and for the following effective dates after that, it should take the earliest of either progression/promotion date, but AFTER the previous row's effective end date.

Any help would be appreciated. Below is the expected output:

Progression date	Progression end date	Promotion date	Promotion end date	Effective date	Effective end date
1/01/2018	25/02/2018	1/01/2018	1/07/2018	1/01/2018	25/02/2018
26/02/2018	24/02/2019	1/01/2018	1/07/2018	26/02/2018	1/07/2018
26/02/2018	24/02/2019	2/07/2018	2/12/2018	2/07/2018	2/12/2018
26/02/2018	24/02/2019	3/12/2018	30/06/2019	3/12/2018	24/02/2019
25/02/2019	23/02/2020	3/12/2018	30/06/2019	25/02/2019	30/06/2019
25/02/2019	23/02/2020	1/07/2019	1/12/2019	1/07/2019	1/12/2019
25/02/2019	23/02/2020	2/12/2019	12/07/2020	2/12/2019	23/02/2020
24/02/2020	7/01/2024	2/12/2019	12/07/2020	24/02/2020	12/07/2020
24/02/2020	7/01/2024	13/07/2020	13/12/2020	13/07/2020	13/12/2020
24/02/2020	7/01/2024	14/12/2020	11/07/2021	14/12/2020	11/07/2021
24/02/2020	7/01/2024	12/07/2021	10/07/2022	12/07/2021	10/07/2022
24/02/2020	7/01/2024	11/07/2022	9/07/2023	11/07/2022	9/07/2023
24/02/2020	7/01/2024	10/07/2023	7/01/2024	10/07/2023	7/01/2024

[Bren_Spill · Accepted solution]

Hi @hcao - you can achieve this with a multi-row formula tool.

To test, create a new field called Effective Date2, making sure that it's date type, and use the following formula:

if isnull([Row-1:Effective Date2]) then min([Progression date],[Promotion date]) elseif

min([Progression date],[Promotion date]) <= [Row-1:Effective Date2] then max([Row-1:Effective Date2],[Progression date],[Promotion date]) else Null() endif

Repeat for end date

You will also need to convert your dates to Alteryx date types for this to work. You can do this for each column using a datetimeparse formula: DateTimeParse([Progression date],"%d/%m/%Y")

[TUSHAR050392 · Accepted solution]

Hey @hcao Another approach -

1. First convert all your 4 columns to date using date time tool.

2. Then use the Record ID field to create an id.

3. Then use a formula tool to create 2 new columns - Effective date - MIN(Progression date,Promotion date) and Effective end date - MIN(Progression end date,Promotion end date)

4. Use Multi row formula tool and update Effective date column and use formula - IF Record Id = 1 then Effective date else MAX(Progression date,Promotion date, row-1: Effective end date) ENDIF

Hope this helps

[hcao]

Thank you both, @Bren_Spill @TUSHAR050392 both solutions have worked wonderfully!