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 · Accepted solution]

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?

[alexloudon]

I feel like such an idiot for not using the datetimeparse formula, thank you.

I do have a solution for the fringe cases, i use a summary statistic to count the number of distinct values in the date/month column. My reports have 30 days of data, so the max number of distinct values in the date column is 3. So currently I have it set up to classify the column as date if the number of distinct values is 3 or less. 

This still allows for the extremely fringe case of a report with only 3 days of data, but for my use case this is not going to happen. 

Thanks for your help.