Formula Based on Dates

[bsolove123]

I am trying to do a formula in the Effective Date Determination column. I am trying to get the workflow to do the follow:

1. If effective date2 is empty, then effective date1.

2. If effective date2 is not empty, but tax year beginning is less than or equal to effective date1, then effective date1.

3. If effective date2 is not empty, but tax year beginning is after effective date1, but before effective date 2, then effective date1.

4. if effective date2 is not empty and tax year beginning is after or equal to effective date2 then effective date2. 

You can see that the formula is not being applied properly to records 2 and 6. I've attached my test data and a screen shot of my formulas. Thanks in advance for your help!

					Workflow Results	Desired Results
Record ID	Tax Year Beginning	Tax Year Ending	Effective Date1	Effective Date2	Effective Date Determination	Effective Date Determination
1	12/1/2019	11/30/2020	1/1/2020		1/1/2020	1/1/2020	TRUE
2	12/1/2019	11/30/2020	1/1/2020	1/1/2021	1/1/2021	1/1/2020	FALSE
3	1/1/2020	12/31/2020	1/1/2020		1/1/2020	1/1/2020	TRUE
4	1/1/2020	12/31/2020	1/1/2020	1/1/2021	1/1/2020	1/1/2020	TRUE
5	6/30/2020	5/31/2021	1/1/2020		1/1/2020	1/1/2020	TRUE
6	6/30/2020	5/31/2021	1/1/2020	1/1/2021	1/1/2021	1/1/2020	FALSE
7	1/1/2021	12/31/2021	1/1/2020		1/1/2020	1/1/2020	TRUE
8	1/1/2021	12/31/2021	1/1/2020	1/1/2021	1/1/2021	1/1/2021	TRUE
9	6/30/2021	5/31/2022	1/1/2020		1/1/2020	1/1/2020	TRUE
10	6/30/2021	5/31/2022	1/1/2020	1/1/2021	1/1/2021	1/1/2021	TRUE

[atcodedog05 · Accepted solution]

Hi @bsolove123 

I would advice you to convert the dates to proper Alteryx dateformat (YYYY-MM-DD). Because currently its treated as a string and not as date. Please use  datetimeparse function to convert it to dates and after that your logic might work.

DateTimeParse()

https://help.alteryx.com/20214/designer/datetime-functions

You can club datetimeprase function with multi-field formula tool to apply it on multiple columns here is an interactive lesson on it.

https://community.alteryx.com/t5/Interactive-Lessons/Multi-Field-Formulas/ta-p/424422

Hope this helps : )