The date is showing differently to the one input

[KatieBrunton]

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

[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!

[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