Alteryx, how many days has it been since I started in Data School that is, from February 7 until today, March 15? "36 days". And how many days old is Data School as of today? "2,457 days".
One of the important parsing tools in Alteryx is the DateTime. Often times, we are confronted with questions about duration, in years, month, days, hours or even up to seconds.
It is usual we have dates in different formats in data sets and Alteryx can handle most of these. But the first thing we must understand about DateTime tool, is that it has to be in the standard Alteryx datetime format and data types.
In my sample data, the dates are strings written in Month dd, yyyy format. This is easily converted into Alteryx datetime format by using the the DateTime parse tool and by providing the exact string format so that it can be interpreted in the standard format. It should be noted that the string format should match down to punctuation and white spaces, otherwise, it becomes a null.
To further break down dates and time , a Formula tool can be used. Before writing expressions, we have to make sure that the dates are correctly classified and assigned to appropriate data types which can be verified with the metadata button in results window.
Referring back to my example, I have calculated the number of days difference from the formatted Start date and date today using the formula DateTimeDiff(DateTimeNow(), [Alteryx Format],'days'), which will return units in days.
There are instance where we just want to get the day of week from the Date or the month of the returned value. To unlock additional information stored in each date, certain specifiers and separators are used to customize outputs.
Specifiers are codes that combines percent sign and case sensitive letter to output certain information like weekday or time in a timezone, to name a few. Separators are symbols like slashes and commas which separates the value of the specifiers. They can produce customize outputs that can be used for further analysis in workflow, reporting and visualization.
So Alteryx, what day of the week was June 23, 2015? "Tuesday." 🧐
Refer to DateTime Function documentation for full list and functionalities of this tool https://help.alteryx.com/20214/designer/datetime-functions