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!

General Discussions

Discuss any topics that are not product-specific here.

DateTime in Formula tool

angelabigham
5 - Atom

How to transform the sales date (44075) to the standard date format (2020-09-01) using the DateTime functions in the Formula tool.

4 REPLIES 4
DataNath
17 - Castor

Wrapping it in ToDate() should do the trick! See below:

 

DataNath_0-1652741930120.png

 

angelabigham
5 - Atom

The output is still null.

KaneG
Alteryx Alumni (Retired)

Try running the workflow to see if it changes. Also, you want to use ToDate() rather than ToDateTime(). You may need to create a new column in order to change the Data Type (Multi-field Formula can get around this).

 

The other method would be using the DateTimeAdd() formula: DateTimeAdd('1899-12-30',[DateNumber],'days'). The reason that 1899-12-30 is used rather than the 31st is that Excel mistakenly thought that 1900 was a leap year back when it was originally developed and so there is an extra day in the calc.

 

KaneG_0-1652750967709.png

 

DataNath
17 - Castor

@angelabigham As Kane says, you’re using ToDateTime() there instead of ToDate() as shown in my screenshot.

Labels