Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Inconsistency for Calculating Excel Date with To Date and DateTimeAdd Functions

Qiu
20 - Arcturus
20 - Arcturus

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.

Qiu_0-1632704314908.png

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")

 

Qiu_1-1632704494007.png

 

3 REPLIES 3
DanielG
12 - Quasar

Hi @Qiu 

SOrry need more time.  Misread your post. 🙂

 

DanielG
12 - Quasar

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
22 - Nova
22 - Nova

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.

Labels