Can someone help me achieve the below excel formula in Alteryx? similarly for month and year as well
@Abhishekjoshua
Please find the below output
Hey @Abhishekjoshua,
By the looks of that formula you are converting a Date to the format of a day with two digits. This can be done with the datetimeformat function like this:
datetimeformat([Q2],"%d")
Would you be able to give some sample data from Q2 for context?
Here is the docs on this function: DateTime Functions | Alteryx Help
Hey @Abhishekjoshua, to recreate the =Text function like this, you'll want to use the DateTimeFormat() function in Alteryx with the %d identifier, for month it'll be %m and %y or %Y for a 2/4-digit year respectively.
If your dates aren't already in ISO format (YYYY-MM-DD) then you'll first need to convert them into that using the DateTimeParse() function. More info on all of that here: https://help.alteryx.com/20223/designer/datetime-functions
Hi @IraWatt, below is the column "Q" sample data in my scenario and the output I need is also attached. Please help. Thank you
Sample data:
Output to achieve:
Please find the expected output.
Check and let me know if it worked as your expectation.
If not, can do some tweaks to get the desired result.
Hi @ShankerV , yes you got it right, that is the expected output
Can I know how did you achieve it? I got the date one right but the month and the year column is troubling.
Please help.
Please note that In your example the "Field1" data is different, hence you got 08, Jan and 2022
In my case the referred column data is in this format "8-Jan-22"
Hi,
One sec, we can work on 8-Jan-2022 also.
Hi @Abhishekjoshua
Please consider the final output as the below. Where I have fine tweaked to work on all different date types.
Explaining what happens in my workflow,
I have used Text to Columns tool, as I see your data is using a delimiter -
Hence the output is split into 3 different columns.
Then the incoming date might be
8-Jan-22
12-Jan-22
15-Jan-2022
the expected output is
08 Jan 2022
12 Jan 2022
15 Jan 2022
Hence used the formula tool to check the condition whether it contains 2 digits, if not used PadLeft option to add 0 in the front.
Same in year also.
Hope this helps!!!! Hope your issue is resolved. Feel free to mark helpful answers as a solution, so that future users with the same issue can find them easier!!!!
Many thanks
Shanker V
@ShankerV I hope this helps, I will try it out and let you know my output. Thank you so much!
@ShankerV Hi, when the column is spilt into 3, how can I name the 3 columns differently?
One more method also, where can use RegEx tool.
By this method, I can reach the solution with just 2 tools highlighted in yellow.
