Convert Different Date Formats in a Single Column to a Standard Date Format

I have a case where I have a column with multiple date formats. If you see below the Date Mask or Format is not consistent.

The last row has .dd-MM-yyyy

The last second row has '.dd-MM-yyyy

The last third row has 'dd/mm/yyyy

so on and so forth

44929

1-Jan-24

1/10/2024

6/Jan/2024

6th Sept 2018

'05/12/2023

'.02-MAY-2023

.02-MAY-2023

In total there are 8 different formats of date

Date.PNG

Workflow

Accepted answers

Gawa

hi @SudhaGupta1 You can convert it to Date field by Formula tool with IF/ELSE conditional statement.

For example, you need to define 8 types if/elseif condition by Regex_Match function, and convert it by DateTimeParse function for each.

As an exception, if the data format is a number (44929), you need to convert it by DateTimeAdd function. Usually that number stands for the day count since 1900-01-01. See the example snap shot how to implement it.

Please note that you cannot sometime distinguish the format, for example between 'yyyy/MM/dd' and 'yyyy/dd/MM'. I mean if the data is '2024-01-02', you cannot judge which 2nd of January, 2024 or 1st of February, 2024. Please be careful.

apathetichell

Try something like - datetimeparse(regex_replace([Field1],"(^\d+)(\w+)(.*)","$1$3"),"%d %b %Y")

All comments

Gawa

hi @SudhaGupta1 You can convert it to Date field by Formula tool with IF/ELSE conditional statement.

For example, you need to define 8 types if/elseif condition by Regex_Match function, and convert it by DateTimeParse function for each.

SudhaGupta1

Hi @gawa ,
I am able to get the expected results for all cases except for 6th Sept 2018 date. How to write formula for it so that it covers similar cases.

SudhaGupta1

Hi @gawa ,
I am able to get the expected results for all cases except for 6th Sept 2018 date. How to write formula for it so that it covers similar cases.

Gawa

@SudhaGupta1

My advice is...try to change format like the following format with help of Regex_Replace function, etc.

6 Sep 2018 or 6th Sep 2018

And apply Date TimeParse function.

apathetichell

Try something like - datetimeparse(regex_replace([Field1],"(^\d+)(\w+)(.*)","$1$3"),"%d %b %Y")

hcook

@apathetichell

Do you know a better formula to convert yyyymmdd to where it is in a date format such as mm/dd/yyyy? For example, my data shows 19650930 and I want it to say 09/30/1965.

My current formula is:

Substring([Date Open],4,2)+Right([Date Open], 2)+Left([Date Open], 4) which gives me 09301965.

Any help is appreciated!

apathetichell

datetimeformat(datetimeparse([field],"%Y%m%d"),"%m/%d/%Y")

hcook

Thank you!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703