Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Problem converting date column with multiple formats

MATTHEW_KING1
8 - Asteroid

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 :).

15 REPLIES 15
atcodedog05
22 - Nova
22 - Nova

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 : )

MATTHEW_KING1
8 - Asteroid

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

binuacs
20 - Arcturus
OllieClarke
15 - Aurora
15 - Aurora

Hey @MATTHEW_KING1 

here's 2 different ways of tackling this:

OllieClarke_0-1644489497758.png

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

 

MATTHEW_KING1
8 - Asteroid

@binuacs Thanks, although i need all to appear in mm/dd/yyy format. How would i amend you formula to achieve that please?

binuacs
20 - Arcturus
MATTHEW_KING1
8 - Asteroid

Thanks @binuacs @OllieClarke @atcodedog05  I will give the methods a try and let you know how i got on. Much appreciated.

 

atcodedog05
22 - Nova
22 - Nova

Hi @MATTHEW_KING1 

 

Just another take

 

atcodedog05_0-1644491704752.png

 

Hope this helps : )

 

MATTHEW_KING1
8 - Asteroid

@atcodedog05 thanks, i will try that also, much appreciated :)

Labels