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

[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.

[Raj]

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

[Raj · Accepted solution]

@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.

[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...

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