Convert serial date format to yyyy-mm-dd

[Ma7moud]

Hi guys,

I m working on a dataset that contains the following column (see attachment). As you can see, the column contains serial number which is an another date format. I want to convert this serial format to the other date format yyyy-mm-dd

The column contains null values as well.

I appreciate your help. 

[Yoshiro_Fujimori]

Hi @Ma7moud ,

If null values should remain unchanged, you can do this with this formula.

Date = 

IF IsInteger([Data])
THEN DateTimeAdd("1900-01-01", ToNumber([Data])-2, "day")
ELSE [Data]
ENDIF

Please try to apply the above to the field you want to convert.

If you find any issue, please let me know.

[binuacs]

@Ma7moud One way of doing this

[Yoshiro_Fujimori · Accepted solution]

Hi @binuacs ,

I forgot the ToDate function. Thank you for the post!

By the way, in this part of my expression:

DateTimeAdd("1900-01-01", ToNumber([Data])-2, "day")

I subtract "2" (not "1") as a result of test.

But there is a historical reason.

I got to know this from Joel Spolsky's book "More Joel on Software".

You can find the original post here: https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/

Microsoft engineer designed to treat Year 1900 as a leap year (actully not), to import data from Lotus 1-2-3 (!).

Interesting, isn't it?😉

[Ma7moud]

Thanks. It wokrs now👍