Date Conversion

[Kaish]

Hi all I have a file in which I want to change the date format.

input data 

45426

Output Data

5/13//2024

Kindly guide me

[Qiu]

@Kaish 
5/14//2024? 😁

[Kaish]

 @Qiu , It's not working, I am getting null values, I have more than 1000 data, the above example was just a sample data, could you guide me how can I overcome this ? I have also changed the data type to date, still not working

[Qiu]

@Kaish 
You have to at least show some of the records giving you null values...😑
The variable for function ToDate() has to be integer.

[Kaish]

Any solution where in I can select up the whole column and convert the data type to proper date ?

[Raj]

@Kaish 
can you add some sample data for the column
as I doubt there might be some other formats present in that column
will be in better position to help.

[Kaish]

Hi @Raj, this is the only sample I can provide, my date has this issue, I want to convert to a proper date that is for the whole column, the column contains dates in these values, I want to convert these values to a proper date.

[Kaish]

In the dataset few data have proper date format "yyyy-mm-dd" and some have this excel number, I am trying to change the data type but still I am unable to et to my output

[cjaneczko]

If its a mix match of date types you will need to use an IF statement to capture all of the date types in the column and how you want them to be output. We need more examples of all the formats available in your column.

[cjaneczko]

Here is another way based on the two formats you had listed. If you are still getting nulls after this, we'll need to know which date formats are yielding null values.

if REGEX_Match([Field1], '\d{5}') then todate(tonumber([Field1])) elseif regex_match([Field1], '\d+-\d+-\d+') then datetimeparse([Field1],'%Y-%m-%d') else null() endif