Date time format

Question

44910
44923
44916
44916
44938
44923
44938
44876
44911
44943
44930
44928
44917
44916
44916
44912
44940
44930
44957
44935
44935
44963
7/2/2023
28/12/2022
31/1/2023
44935
44970
44880
44896
44958
17/1/2023
29/11/2022
28/11/2022
13/2/2023
44960
44965
44959
44984
44961
44994
44972
44995
44989
44992
44953
44953
44938
45009

Above are the date data I obtained from an Excel file, I am not supposed to format cell nor hardcode the data but it will show null for those in such format: 7/2/2023. But if I tick the "First row contains data", my header will become one of the data and I still have no clue how to convert them into a uniformed format.

Yoshiro_Fujimori · Accepted Answer

Hi @imivan ,

You can apply the date function for each pattern as below.

I hope this helps.

Workflow

Date Functions in Formulat tool

Output =

IF REGEX_Match([Data], "\d+")
THEN ToDate(ToNumber([Data]))
ELSEIF REGEX_Match([Data], "\d+\/\d+\/\d{4}")
THEN DateTimeParse([Data],"%d/%m/%Y")
ELSE Null()
ENDIF

Output