Date Conversion - Regx

Sarath27

Hi All,

Could you please help converting the below Balance Date to the format given in Balance Date(New) in one single formula?

My source date usually has this date format.

Balance Date Balance Date (New)

2024-12-06 2024-06-12

06/13/2024 00:00:00 2024-06-13

Thanks,

Sarath

Data Investigation

Accepted answers

Raj

@Sarath27
IF ISNULL(DateTimeParse([Balance Date],"%Y_%m_%d")) THEN
DateTimeFormat(DateTimeParse([Balance Date],"%m/%d/%Y %H:%M:%S"),"%Y-%m-%d")
ELSE
DateTimeFormat(DateTimeParse([Balance Date],"%Y_%m_%d"),"%Y-%m-%d")
ENDIF

Hope this helps
mark done if solved.

All comments

Raj

@Sarath27
use this formula

IF ISNULL(DateTimeParse([Balance Date],"%Y-%m-%d")) THEN
DateTimeFormat(DateTimeParse([Balance Date],"%m/%d/%Y %H:%M:%S"),"%Y-%m-%d")
ELSE
DateTimeFormat(DateTimeParse([Balance Date],"%Y-%m-%d"),"%Y-%d-%m")
ENDIF

mark done if solved

Sarath27

Hi @Raj Thanks for your prompt response.

Please find the screenshot attached for the results using your formula.

Date.PNG

Raj

@Sarath27
you are using "_" here but you asked for "-"
this is the reason of null value

Raj

Sarath27

As you can see in the attachment, the date format I want is YYYY-MM-DD, but the first row came out as YYYY-DD-MM.

Gawa

hi @Sarath27
Similar question was raised before. Refer to the below thread and solutions.
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Convert-Different-Date-Formats-in-a-Single-Column-to-a-Standard/m-p/1250961#M311594

Basically this is a challenging task, because you cannot distinguish '2024-12-06(=12th of June)' and '2024-12-06(6th of December)'.

Sarath27

@Raj Thanks for your solution. I just tweaked this formula, it worked. Thanks much

IF ISNULL(DateTimeParse([Balance date],"%Y-%d-%m")) THEN
DateTimeFormat(DateTimeParse([Balance date],"%m/%d/%Y %H:%M:%S"),"%Y-%m-%d")
ELSE
DateTimeFormat(DateTimeParse([Balance date],"%Y-%m-%d"),"%Y-%d-%m")
ENDIF

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703