convert string to date but with different date format

[Sshasnk]

Hi I have a string dates like below and I want to change it into date data.

2 format of data is present below.

1. yyyy-mm-dd

2. dd/mm/yyyy

Date

2020-01-17

7/07/2022

2021-02-16

15/03/2023

Now 2 changes we have to do in the above input

1. Change String to date (Data Type)

2. Change the format to yyyy-mm-dd

Output:

Date

2020-01-17

2022-07-07

2021-02-16

2023-03-15

[ShankerV]

Hi @Sshasnk 

Please use Multi field formula.

Also use datetimeparse to convert the date.

IF contains([_CurrentField_],"/")
THEN datetimeparse([_CurrentField_],"%d/%m/%y")
ELSE [_CurrentField_]
ENDIF

Many thanks

Shanker V

[jrgo]

Check this Macro out.

https://community.alteryx.com/t5/Community-Gallery/BB-Date-Python-Macro/ta-p/1043041

[DataNath]

Hey @Sshasnk, the following expression should do the trick:

IF Contains([Date], '/') THEN DateTimeParse([Date],'%d/%m/%Y') ELSE DateTimeParse([Date],'%Y-%m-%d') ENDIF

[RobertOdera]

Hi, @Sshasnk 

It is a little more nuanced because you have the date 7/07/2022 (other incoming dates taking this format could be of form MM/dd/YYYY or dd/MM/YYYY).

You will need to FIRST separate each incoming format to PROFILE its population of dates and then determine and deploy the applicable treatment, before unioning back into a standardized Date output stream (e.g. is 6/08/2023 = Jun 6th or Aug 6th?)

However, for your limited use case below, consider the below which has some QC built-in. I hope you find it helpful - Cheers!