Convert serial date format to yyyy-mm-dd

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.

digit column.png

Date Time

Accepted answers

Yoshiro_Fujimori

Hi @binu_acs ,

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?😉

All comments

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.

binu_acs

@Ma7moud One way of doing this

Date_080523.yxmd

Yoshiro_Fujimori

Hi @binu_acs ,

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?😉

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335