The date is showing differently to the one input

HI, I have brought in a data set that has a date column in it, some of which are blank. For the blank ones, I want to default the same date. I have created the formula below, but for some reason it is converting my date to 1905:

if IsNull([Leave Date]) then 2025-03-31 else [Leave Date] endif

But the date that is showing in the output is: 1905-06-13

Please can somebody say where I am going wrong?

Many thanks

Date Time

Accepted answers

All comments

alexnajm

Put your date in quotes - "2025-03-31"

dataguyW

If you want to enter the dates in the standard format then @alexnajm is correct. If you want to explicitly create a date you can use ToDate("2025-03-31").

alexnajm

@dataguyW it will still work to be stored as a Date if the Formula tool specifies Date as the data type output!

Screenshot 2025-06-18 164543.png

dataguyW

You are correct. Missed that piece.

alexnajm

All good!

alexnajm

@KatieBrunton please mark the helpful response as the solution, or let us know if something is still not working!

AlteryxTrev

Both Alex and Join above hit the nail on the head. I did want to add one extra layer explain why your formula converted it to the date of 1905-06-13. I couldn't replicate it with your exact formula, but here is the reason nonetheless:

Using your exact formula returned a null with this warning, "...Date/Time fields do not support Conversion from Int64...". Essentially, Alteryx is doing math to subtract the values you gave it, coming up with the integer 1989 and was not interpreting it as a date, just a number. If you wrapped your subtraction with this formula ToDate(2025-03-31), it is first doing the math to get ToDate(1989) and it then converts this to "Return me the date that is 1989 days beyond 1900-01-01" which is the 1905-06-13 date.

Hope this helps!

AlteryxTrev

The others have provided the solution above, however, I did want to take a moment to explain why Alteryx gave you back the result that it did. In short, what you gave it was a math problem 2025-03-31 which is equal to 1,989. Alteryx took this value (1,989) and added it to the date of 1900-01-01 which is the base date it uses. When you add this many days you get back 1905-06-13.

The reason why the above solutions work is because it allow Alteryx to interpret your typed date as a string, instead of just integers to subtract from one another.

If its helpful, I've also created a short video also explaining the above. https://youtu.be/lUlIFaB5cwA

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703