Null values when using DateTimeAdd function are not correct

I have a workflow that I used the DateTimeAdd fuction, because when coming into Alteryx it was coming up as 44183.563345 etc.

I used DateTimeAdd('1900-01-01',[INITIATIVE_DUE_DATE] - 2 ,'days') and it works great, except for the null values in my original data set are now coming back 1900-01-01. I tried to use the data cleansing tool to have those null fields blank, but it does not work.

Preparation

Accepted answers

HomesickSurfer

Hi @jjenny50

Since you are successfully converting to proper dates for the non-Null values, try this on the DUE DATE field:

ToString(Replace([DUE DATE], '1900-01-01', ""), 0)

All comments

atcodedog05

Hi @jjenny50

You can ToDate([Date]) or ToDateTime([Date]) function like below

Workflow:

Hope this helps : )

todate.yxmd

csmith11

Solution above is great!

jjenny50

For some reason that does not work for me -now everything comes back as null. When my date field originally comes in as a double. I changed it with a select tool to V_WString. then do the the formula provided.

HomesickSurfer

Hi @jjenny50

Since you are successfully converting to proper dates for the non-Null values, try this on the DUE DATE field:

ToString(Replace([DUE DATE], '1900-01-01', ""), 0)

jjenny50

That worked. Those fields are now blank. Thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703