Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Date Format Changing

SH_94
11 - Bolide

Dear Community,

 

I would like to ask how can we change the date format as per data below:

Data 

1. Jun- 2021

2. March-2021

3. 2021 June

 

Result wanted

1. 2021-06

2. 2021-03

3. 2021-06

 

Many thanks for your helps

5 REPLIES 5
JosephSerpis
17 - Castor
17 - Castor

Hi @SH_94 you will need to test for each format your dates appear and then have to perform a datetimeparse for each format and then convert them into the format you are asking for. I've used Regex to test for the format and then used datetimeparse and then datetimeformat. I came up with two approaches one that shows the steps I've taken and another where I do everything in one formula. The Datetime Function help documentation is great for the different specifiers for Datetimeparse and Datetimeformat.

 

Datetimeformat_11072021.JPG

 

 

SH_94
11 - Bolide

Dear @JosephSerpis ,

 

Thank you for the prompt response.

 

I would like to clarify with you on the following points:

1. "\w{3}-\s\d{4}"    : May i know what is the impact or function if we added 3 and 4 in this formula? If we put wrong the number , will there be any impact?

 

2. \w*-\d{4}"   : May i know what the "w" mean in this case?

 

3. The formula for each are different : "%b- %Y", "%B- %Y" ,"%Y %B" . Could you briefly explain on this?

 

Many thanks for your help.

JosephSerpis
17 - Castor
17 - Castor

Hi @SH_94 the below screenshot which I've taken from the Regex tool will show what the different characters I used in the regex formula are.Datetimeformat_11072021_V1.JPG

 

The numbers in the {} represent the exact number I'm looking for to find in your string so \d{4} will look for four digits.

 

For your last point you have three different date formats so there needs to be three different formats I'm looking for the datetime function help I shared in my earlier post will explain the differences in the specifiers e.g. %b and %B for instance. 

SH_94
11 - Bolide

Dear @JosephSerpis ,

 

Thanks a lot for your prompt response.

 

May i know how you will build the formula for the data format below :

Data

1. 44542

2. 12/12/2021

 

Results

1.2021-12-12

2.2021-12-12

 

Thank you.

JosephSerpis
17 - Castor
17 - Castor

Hi @SH_94 I amended the workflow to account for those two formats. The format 12/12/2021 is pretty easy to resolve I'm assuming it's DD-MM-YYYY format. The regex function REGEX_Match([Field1], "\d{2}/\d{2}/\d{4}") will look for that two digits / two digits / four digits and then the Datetime function DateTimeParse([Field1],"%d/%m/%Y") will parse the string. If the format is MM-DD-YYYY then swap around the d and m in the datetimefunction.

 

The 44542 is more tricky as this excel representation of date therefore I use REGEX_Match([Field1], "\d*") to test if a value is all numbers and use a different function called ToDate(tonumber([Field1]) plus a function called to number to ensure the value is numeric in the argument as the data type of column is string due to the different formats present.

 

 

Labels