Date Format issue

[Lalaiah]

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 Name	Date Format
QCT	5/22/2021
CORP	01/30/2021
CLOUD	25-Nov-16

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

[MarqueeCrew]

Sipping (not stopping). 

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

[Lalaiah]

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]

@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]

@Lalaiah ,

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

Then you should get there.

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

M.

[atcodedog05]

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.

You can ignore the warnings its expected.

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

Hope this helps 🙂

[Qiu · Accepted solution]

@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.

[mceleavey]

Yeah, I agree with @Qiu and @atcodedog05 .

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

M.

[atcodedog05]

Hi @Qiu 

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

[Qiu]

@atcodedog05 

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

[atcodedog05]

I guess warning messages are still bit intimidating 😅