Date Format issue

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

Developer

Accepted answers

Qiu

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

date format-r1.yxmd

All comments

atcodedog05

Hi @Lalaiah

You can use a if else block check for date format does it contain "-" or so based on it use dateparse function.

IF Contains([Date Format], "-") 
THEN DateTimeParse([Date Format],"%d-%b-%y")
ELSE DateTimeParse([Date Format],"%m/%d/%Y") 
ENDIF

Hope this helps 🙂

date format.yxmd

mceleavey

Hi @Lalaiah ,

I've used the DateTime tool to convert, then merged the different versions.

Hope this helps,

mceleavey_solution.yxmd

MarqueeCrew

@Lalaiah ,

let's try:

if substring ([date field], 2,1) = "-" then

datetimeparse([date field],"%d-%b-%y")

else

datetimeparse([date field],"%m/%d/%Y")

endif

cheers,

mark

https://community.alteryx.com/t5/Engine-Works/MarqueeCrew-s-Guide-to-Avoiding-Date-Frustration/ba-p/181500

atcodedog05

Hi @MarqueeCrew

@MarqueeCrew wrote:

https://community.alteryx.com/t5/Engine-Works/MarqueeCrew-s-Guide-to-Avoiding-Date-Frustration/ba-p/181500

This macro would be nice and handy for date issues 🙂

Lalaiah

Thank you for your quick response !!!

Getting the same error .. I'm attaching an example file can you please sort it out.

Thanks in advance.

Regards,

LAlaiah

Book2.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703