Date Format Changing

[SH_94]

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

[JosephSerpis · Accepted solution]

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.

[SH_94]

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]

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.

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]

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]

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.