How to set the date to a dynamic date anchored to end of month in a filter or formula

[alteryxlim]

Hi,

I'd like to create a workflow whereby each month, Alteryx will filter or prepare a list of action owners who need to close out their action items by the end of that month. I have my data source (Excel).I can do a manual filter and set the date, but I want an automatic (dynamic) instruction in Alteryx that will automatically set the criteria to end of the month (or last day of that month).

What is the best way to achieve this? 

--HK

[JosephSerpis]

The function DatetimeTrim is perfect for this example as you can specify last of the month.

[atcodedog05]

Hi @alteryxlim 

Use a formula in a filter like this

Hope this helps : )

[alteryxlim]

Thanks  @atcodedog05

What is wrong with what I have in the screenshot?

[NikyN]

Hello @alteryxlim,

For sure the first part of the condition --- formula ToDate([Target Completion Date]) --- if the Target Completion Date is not in the date format, than you need to use DateTimeParse(dt,f) function to change you date to date format.

Eg. if your date is in format 18 Feb 2021, than with formula DateTimeParse([DATE],'%d %b %Y') you can change it to date format.

Best Luck!

Niky

[atcodedog05]

Hi @alteryxlim 

You can Todate() for both but its a must for trim function since it gives output in datetime

ToDate([Date])=ToDate(DateTimeTrim([Date],"lastofmonth"))

As @NikyN if the date is not in Alteryx dateformat you would need to use parse function to convert it to date.

Hope this helps : )

[alteryxlim]

Thanks @atcodedog05 @NikyN

It works now.

Is it correct to say that I must ToDate the result of DateTimeTrim because after applying the DateTimeTrim function, the output might not be recognized by Alteryx as a data format, hence I will need to ToDate(DateTimeTrim[TargetCompletionDate],"lastofmonth") ? 

[atcodedog05]

Hi @alteryxlim 

Just using lastofmonth will give you the last minute of the month like below 2022-02-28 23:59:59 since our date doesn't have time it wont match when we use ToDate it becomes 2022-02-28 (I,e considering only datepart). This way now it will match.

Hope this helps : )