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

[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 🙂

[mceleavey]

Hi @Lalaiah ,

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

Hope this helps,

M

[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/... 

[atcodedog05]

Hi @MarqueeCrew 

---

@MarqueeCrew wrote:

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

---

This macro would be nice and handy for date issues 🙂

[Qiu]

@atcodedog05  @MarqueeCrew  @mceleavey 
It is rare to see big shots working on the same question. 😁

[mceleavey]

 

[Lalaiah]

Hi 

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 

[mceleavey]

Hi @Lalaiah ,

Sorry, what's the problem you're having? What was the error?

M.

[MarqueeCrew]

@Qiu 

I'm a fan of not using the date time tool. I think I've seen more data questions still than regular expression questions. Certainly they are amongst the top 10 questions on community. 

in my early use of Alteryx I would use string functions to construct dates. Now I refer to date specified and have them on speed dial. 

stopping in my coffee, a date question is a familiar way to start my day. 

cheers,

Mark