Put a specific date base on date type

[rafatomillero]

I have a column called Invoice Date with mixed date formats, including some NULL, e.g.: 12/05/2022 or 22-05. I want to change the ones with format yy-MM (22-05) and change it to dd/MM/yyyy where dd is 01 (01/05/2022). How do I achieve this?

[DataNath]

Here's one way I can think to do that @rafatomillero - let me know if you have any issues when testing it with a wider data set. You'll just need to replace [Mixed date] with your [Invoice Date] column in the expression of course (edit: included below).

IF REGEX_Match([Mixed date], '\d{2}-\d{2}') THEN DateTimeFormat(DateTimeParse([Mixed date], '%y-%m'),'%d/%m/%Y') ELSE [Mixed date] endif

Your expression:

IF REGEX_Match([Invoice Date], '\d{2}-\d{2}') THEN DateTimeFormat(DateTimeParse([Invoice Date], '%y-%m'),'%d/%m/%Y') ELSE [Invoice Date] endif

[rafatomillero]

Hi Nathan,

Thank you for your reply.

I copied and pasted the code you provided, and Alteryx doesn't give me any error. However, I cannot see any changes in my output file.

Please let me know if you know the reason why.

Kind regards,

Rafa

[DataNath]

@rafatomillero are you able to share your workflow, perhaps with mock data, or show a screenshot? If you aren't getting an error then I'm not sure why the changes wouldn't be applied. Is there any chance those records have extra whitespace in them or any other characters etc? Like ' 22-05' or '22-05 ' - something like that would throw off the expression.

[rafatomillero]

I just realised that the dates are '22-05 ' with a space at the end. How do I amend this in the code above?

[DataNath · Accepted solution]

Could just add a data cleanse tool ahead of the formula tool to clean up the field. However, to avoid extra tools you just need to add a space to the pattern that the Regex_Match function is looking for so should be this instead:

IF REGEX_Match([Invoice Date], '\d{2}-\d{2} ') THEN DateTimeFormat(DateTimeParse([Invoice Date], '%y-%m'),'%d/%m/%Y') ELSE [Invoice Date] endif

[rafatomillero]

It worked :-) thank you very much! you saved me plenty of time.