Date Format Issue

[SH_94]

Hi Community,

I had encountered the original data date which contain the following:

May i know how can we use Alteryx to identify if there is any date format as shown as above and update it to the correct date format : 2020-11-20.

Thank you for your time

[Qiu · Accepted solution]

@SH_94 
We may have to do the brutal way.

[SH_94]

Hi @ Qiu,

Can you briefly explain when do we need to use REGEXMATCH function and how to use it as it seems complex to build the formula? I would like to understand it so that i can edit and tailor to my data . Can we use this REGEXMATCH formula as well if we have the data date below?

Previously i use the DateTimeParse to edit the data as below and it give me the result as below. Could you please advise how to edit it?Basically i want the result in date but the data produce give me result in numbers

[Qiu]

@SH_94 
The row#2 and #5 are not possible to parse, OK. maybe possible for these two data.

But when it comes to 2020-04-05, there is no way we can know which is day, which is month.

RegEx_match is to search the patttern with Regex, if fits then parse.

[SH_94]

Hi Qiu,

If that is the case, may i know how can we solve it for the format below ?as i got plenty of data shown as per the screenshot below .

Is there any way we can segregate it out and build another formula for this type of data?

Thank you.

[Qiu]

@SH_94 
What is the Product Result(Date)?

Which one you want to convert? the first column or the second?

[Emil_Kos]

Hi @SH_94,

I have found this post in the community:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-excel-numeric-date-to-date-for...

Thanks to that I have created this workflow:

If you want to change the number (44155) you just need to use the below formula:

ToDate([date])

Output:

[Qiu]

@Emil_Kos 
Nice one.

Until now, I learned that ToDate Fucntion can be used to convert UNIX Epoch time

[SH_94]

Hi Qiu ,

I actually want change the first column from string to date but after i applying Date Timeparse, it become number at the end.

[Emil_Kos · Accepted solution]

Hi @SH_94,

The first column: 2020-11-20 is in actual date format so it should be working.

If it will not work you can use this formula:

DateTimeParse([Field1],'%y-%m-%d')