Inconsistency for Calculating Excel Date with To Date and DateTimeAdd Functions

[Qiu]

I noticed that there is something that I could not understand when using ToDate and DateTimeAdd function to calculate the Excel Date format.

Am I missing something here?
Any insights would be appreciated.

As per Excel help

Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. You will need to change the number format (Format Cells) in order to display a proper date.

For January 1, 1900 is serial number 1, Alteryx  2021.3.2.54175 gives below results.

Note I use 

DateTimeAdd("1900-01-01",[SerialNumber]-1,"days")

So the Todate function is not giving correct answer from what I see.

Then for January 1, 2008 is serial number 39448

This time, ToDate function is giving correct one, while DateTimeAdd is not.
DateTimeAdd("1900-01-01",[SerialNumber]-1,"days")

[DanielG]

Hi @Qiu 

SOrry need more time.  Misread your post. 🙂

[DanielG]

You question triggered flashbacks.  Take a look at this https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Inconsistency-between-DateTimeAdd-and-... which is sort of related.  Perhaps it can help with understanding.  I am not completely understanding it myself but I wonder if what is happening with the date going back to 12/31/1899 is sort of related to how it was rolling back months for the original poster in that link.  I think it has something to do with the "0" time which gets attached but I could be wrong.  I just know that dates are never a good thing to calculate because there is really no such thing as a month because there are different # of days in a given month and there are never 52 weeks in a year, even though that is what everyone says.  🙂

[atcodedog05]

Hi @Qiu 

Even I am curious about this use-case not able to figure out. 🤔

I would suggest you to raise a support ticket at support@alteryx.com. And let me know how it goes.