Standarizing input date

[remarsha]

I have an the input data file running in to thousands of rows.

Challenge:
There is a date coulmm (V_String) which is not uniform. There are two types of formats in the incoming date

1*. dd/mm/yyyy
2#. yyyy-dd-mm.

I need to standardize these to a single format.

* when i am using the String to Date to convert either to
              - mm/dd/yyyy it throws Null.or any other similar formats
            - dd/mm/yyyy it changes the format to yyyy-mm-dd (guess i am converting to the same thing here)

# In both the above conversions the rows with this date format throws null.

How do i standardize the date?

[ShankerV]

Hi @remarsha 

Please make use of the IF function in the formula tool.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @remarsha 

Please find the sample use case for your scenario.

This will work irrespective of the number of lines.

IF REGEX_Match([Field1], "\d{2}/.+")
THEN DateTimeParse([Field1],"%d/%m/%y")
ELSE DateTimeParse([Field1],"%y-%d-%m")
ENDIF

 

 

Many thanks

Shanker V

[flying008 · Accepted solution]

Hi, @remarsha 

FYI.

IIF(Contains([Txt], '-'), DateTimeParse([Txt],'%Y-%d-%m'), DateTimeParse([Txt],'%d/%m/%Y'))

[remarsha]

Thanks Shanker.It worked.

[remarsha]

Thanks this too works.

Intersting, how did u animate?

How is it different from using a RegExp considering both are doing the same thing.?

[flying008 · Accepted solution]

Hi, @remarsha 

1- It is a gif.

2- The purpose of both regular and regular formulas here is to determine the format of the source data, such as whether it contains the symbol '-', and generate the corresponding date format based on logical conditions

[remarsha]

Noted. Thank You!