Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Date Format issue

Lalaiah
8 - Asteroid

Hi,

Consolidated 3 files but the date formats are different for each file so when I convert into date format with help of the date-time tool. however, I am getting below error."ConvError: DateTime (82): Payment Due Date1: Cannot convert "06/30/2020" to a date/time with format "%Y-%m-%d": Month number is out of range 1..12: '30/2020' Record #48433"

 

File NameDate Format
QCT5/22/2021
CORP01/30/2021
CLOUD25-Nov-16

 

I need a "YYYY/MM/DD" format So, Can you please help me out

19 REPLIES 19
MarqueeCrew
20 - Arcturus
20 - Arcturus

Sipping (not stopping). 

ps I answered the post and made these replies via my iPhone. 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Lalaiah
8 - Asteroid

Hi,

 

The below error i am facing.

 

"ConvError: DateTime (4): DateTime_Out2: Cannot convert "2021-05-26" to a date/time with format "%d-%b-%y": Expected separator '-%b-%y', got: '21-05-26' Record #10"

 

"ConvError: DateTime (3): DateTime_Out: Cannot convert "2021-05-26" to a date/time with format "%m/%d/%Y": Month number is out of range 1..12: '2021-05-26' Record #9"

 

I need "YYYY/MM/DD" date format

 

attached reference file.

 

 

regards,

lalaiah

 

Qiu
21 - Polaris
21 - Polaris

@Lalaiah 

Let take a further look

 

If I may jump in this party, after I drag your excel file ot the cavas, it is already ISO Date format.

so what you need is only to change it to another format?

mceleavey
17 - Castor
17 - Castor

@Lalaiah ,

 

taking @MarqueeCrew 's point earlier, the DateTime tool can be fiddly. However, if you add a DateTime tool to this configured as follows:

 

mceleavey_0-1621946783217.png

 

Then you should get there.

 

I've updated my previous solution which I've attached.

 

M.



Bulien

atcodedog05
22 - Nova
22 - Nova

Hi @Lalaiah 

 

This how i would do it. It seems like you have lot of data formats we need to check them and parse them.

 

 

IF !IsNull(DateTimeParse([Payment Due Date],"%d-%b-%y")) 
THEN DateTimeParse([Payment Due Date],"%d-%b-%y")
ELSEIF !IsNull(DateTimeParse([Payment Due Date],"%m/%d/%Y")) 
THEN DateTimeParse([Payment Due Date],"%m/%d/%Y")
ELSEIF !IsNull(DateTimeParse([Payment Due Date],"%Y-%m-%d"))  
THEN DateTimeParse([Payment Due Date],"%Y-%m-%d")
ELSE Null() ENDIF

 

 

I above formula checks if the date is in the mentioned format yes if parse with that format if not got to next format. You might need to add more below blocks if you get more formats.

 

ELSEIF !IsNull(DateTimeParse([Payment Due Date],"%Y-%m-%d"))
THEN DateTimeParse([Payment Due Date],"%Y-%m-%d")

 

Workflow: the way to check whether the date is parsed or not is by checking are they null. If they are not null then they are parse. If null you might need to add more formats in elseif block(above) to match the format.

atcodedog05_0-1621946660541.png

 

You can ignore the warnings its expected.

atcodedog05_1-1621946767410.png

 

Check the dates is all not all. If yes you are good to go.

 

Hope this helps 🙂

Qiu
21 - Polaris
21 - Polaris

@Lalaiah 
Agree with @atcodedog05 , it seems your actual data is having different format with your sample given.
Anyway, this has to be a burtal force for me.

A1.PNGA2.PNG

mceleavey
17 - Castor
17 - Castor

Yeah, I agree with @Qiu and @atcodedog05 .

 

This is another example of why Excel is not a database.

 

M.



Bulien

atcodedog05
22 - Nova
22 - Nova

Hi @Qiu 

 

Just curious, any specific reason for using nested if blocks instead of else-if blocks  🤔

Qiu
21 - Polaris
21 - Polaris

@atcodedog05 

You got me.
not really good at that IIF statements😁

atcodedog05
22 - Nova
22 - Nova

I guess warning messages are still bit intimidating 😅

Labels
Top Solution Authors