Problem converting date column with multiple formats

[MATTHEW_KING1]

My input file is a report(.csv  from Discoverer plus which gives me an end date column amongst other columns. I have set the format to mm/dd/yyyy in discoverer, but for some reason some cells come through as General format, and the rest as Date format. The ones in general format come through as i need and although the ones in date format look correct, they are positioned to the right of the cell on the input file, and when the output file is produced it gives me the format yyyy-dd-mm (2020-08-07). I have tried using the DateTime tool to correct, but i either get blanks on everything except those i need to correct(also they seem to come out as dd/mm/yyyy, so would need flipping again). I am hoping someone will have a solution for this as it is proving very troublesome with my reporting. The only true fix i have managed is opening up the input file in excel, using text to columns, then selecting MDY, then going into format cells, United States locale, and selecting m/dd/yyyy, and then using the formula =text(A2, "mm/dd/yyyy). Then copying the whole column and pasting over it. Only then does my output come out the way i need. Obviously this is madness and i need some help please :).

[MATTHEW_KING1]

@atcodedog05 just used this but it changed the cell with 2021-09-06 to 09/06/2021 when it needs to be 06/09/2021. Is there a way around this, whilst keeping all the other results as is? This is the whole problem i am having, changing those in the yyyy-dd-mm format to mm/dd/yyyy to match the rest, without messing up those already in that format! :)

[atcodedog05 · Accepted solution]

Hi @MATTHEW_KING1 

Surely just need to change the format to dd/mm/yyyy refer below

Hope this helps : )

[MATTHEW_KING1]

@atcodedog05 yes that seems to work. My only worry is that if i have a date of say 2022-01-15, it will then convert that to 15/01/2022. But i am hoping not as all those rogues cells seem to be in the same format.:)

[atcodedog05]

Hi @MATTHEW_KING1 

You can do something like this. Then again as you said there are rouge dates so need to be careful.

Hope this helps : )

[MATTHEW_KING1]

@atcodedog05 thanks again, i think the previous solution should be sufficient in this case, as the rogue format does seem to be yyyy/dd/mm, so that would flip it to mm/dd/yyyy.

[atcodedog05]

Happy to help : ) @MATTHEW_KING1 

If our response helps please don't forget to mark it as solution.

Note: you are free to mark more than one post as solution

Cheers and have a nice day!