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 :).
Solved! Go to Solution.
Hi @MATTHEW_KING1
Can you provide some sample input and expected output It will help us get a better understanding of the usecase.
We will be happy to help : )
Hi @atcodedog05 thanks for your quick reply. Please see attached. The highlighted cells are those that are coming across with the date format and coming out on my output file as yyyy/dd/mm
Hey @MATTHEW_KING1
here's 2 different ways of tackling this:
You can either split your data with a filter, and then union it back together once it's been parsed. Or you can make use of the datetimeparse() function in a conditional formula.
Hope that helps,
Ollie
@binuacs Thanks, although i need all to appear in mm/dd/yyy format. How would i amend you formula to achieve that please?
Thanks @binuacs @OllieClarke @atcodedog05 I will give the methods a try and let you know how i got on. Much appreciated.
@atcodedog05 thanks, i will try that also, much appreciated :)