Alteryx Designer Desktop Discussions

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

Date conversion issues

AbhijeetChib
8 - Asteroid

Good Day Everyone ! 

 

I have an excel file where there are 2 date formats. 

1. 28/02/2023 23:50:11
2. 2023-01-03 00:01:09

I need to subtract these dates from DateTimeToday() to calculate the ageing. 

 

While the first format works fine. I get null values for the second format. I get conversion error 

ConvError: Formula (37): DATETIMEPARSE: Cannot convert "2023-01-03 00:01:09" to a date/time with format "%y%m%d" and language "English": Expected a number for Month: '-01-03 00:01:09'

 

Any ideas on how do i change both the formats into the corrrect one and calculate the ageing. 

 

 

AbhijeetChib_0-1678081446307.png

 

2 REPLIES 2
Yoshiro_Fujimori
15 - Aurora

@AbhijeetChib ,

I would use IF clause to choose the correct formula as below.

IF Contains([sys_created_on], "/")
THEN DateTimeParse([sys_created_on],"%d/%m/%Y %H:%M:%S")
ELSE DateTimeParse([sys_created_on],"%Y-%d-%m %H:%M:%S")
ENDIF

(In the above, I checked if the string contains "/" or not.)

Yoshiro_Fujimori_0-1678083191957.png

Hope this may be of some help. Good luck.

binuacs
20 - Arcturus

@AbhijeetChib Use the below formula to convert into the date format 

 

IIF(Contains([sys_created_Date], '/'), DateTimeParse([sys_created_Date], '%d/%m/%Y'),IIF(Contains([sys_created_Date], '-'), DateTimeParse([sys_created_Date],'%Y-%m-%d'), Null()))

 

binuacs_0-1678083390385.png

 

 

 

 

 

Labels