Using output to update datetime field

alexloudon

Hi everyone,

I've been building a macro that processes reports where the date column (as a string), doesn't always have the same format. Sometimes its yyyy-MM-dd or MM/dd/yyyy etc. I've built the flow to identify what the date format is and return the correct format as a string, however I thought it would be easy to input that with an action tool to update the date format field in a string to datetime tool. However, to my disappointment, I found this wasn't easily possible. 

Does anyone have a solution to this problem? 

Thanks,

Alex

Gawa

hi @alexloudon From technical point of view, you can use Formula tool or DateTime tool as you should have already tried. The problem here is how should we distinguish the format between yyyy-MM-dd and yyyy-dd-MM, for example, 2024/01/02 is:

2nd of January, year2024 in yyyy-MM-dd format

1st of February, year2024 in yyyy-dd-MM format

When we handle the unknown format of date field, we always have to take care of this issue. Does your dataset have any additional information to narrow down the possible format?