Date conversion issues

[AbhijeetChib]

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. 

[Yoshiro_Fujimori · Accepted solution]

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

Hope this may be of some help. Good luck.

[binuacs · Accepted solution]

@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()))