on 11-19-2013 11:08 PM - edited on 07-27-2021 11:47 PM by APIUserOpsDM
To begin, there are many functions within Alteryx that can be performed within the Formula Tool. We try to cover as many as possible in the form of Conditional, Conversion, Spatial, and for this article, DateTime. For any Date or DateTime field within Atleryx, you have the ability to modify or update that field directly within the Formula Tool. For example, let's say you want to find the per day average of sales of the previous month. With the DateTime functions, you can determine the last day of the previous month to find out how many days were in that month, then divide that by total sales. This is of course a simple example, but you get the point.
Moving on! You may be thinking that the built-in functions of the Formula Tool would cover something like this. In a sense, you are correct. There is a function called DateTimeLastOfMonth(), which will return the last day of the current month. The issue is that there are no additional parameters for this particular function, such as the last day of a particular month. In order to properly find the last day of any previous (or future) month, we have to nest this function within another function, DateTimeAdd(). This function allows you to add or subtract minutes, hours, days, months, or years to a given DateTime field. In our case, rather than simply stating DateTimeLastOfMonth(), we want to determine the first day of the month, minus one day. Luckily, we have another function called DateTimeFirstOfMonth() that allows us to do just that. The final result:
DateTimeAdd(DateTimeFirstOfMonth(),-1,"Days")
The result is 2013-10-31. Simple, huh? Even better, let's say we need to determine the last day from two months ago. Since we can't dynamically say -30 days, or -31 days depending on the current month, all we need to do is nest in one more DateTimeAdd() function:
DateTimeAdd(DateTimeAdd(DateTimeFirstOfMonth(),-1,"Months"),-1,"Days")
The result is 2013-09-30 (if you are running the module in November). By nesting in another DateTimeAdd() function pulling the first day of the previous month minus one day, you end up with your desired result.
As previously mentioned, there are many functions within Alteryx that can be performed for a variety of reasons. The DateTime function we touch on here is just a drop in the bucket for what we have the ability to do, but is a good demonstration of some of the incredible yet simple flexibility within Alteryx.
Until next time! -Chad
For more tips, tricks, and general Alteryx inquiries, follow me on Twitter! @AlteryxChad
Use the DateTimeFirstOfMonth function.
Hi David,
To get first date of the Month
DateTimeTrim([Your Date Field],'firstofmonth')
To get last date of every month
DateTimeTrim([Yout Date Field],'lastofmonth')
Thanks Harsha! that's even easier than what i came up with!
These methods are great to find the last day of a month a certain distance away from the current date of running the workflow, but what if I want to find the last day of a month with no dependence on the date at which I'm running the workflow.
For example, how can I find the date of the last day of December 2016 when I run the workflow in March of 2017 and when I run it in October of 2025 using the same formula?
@Ferrari I have just viewed your message and for the month of August I am getting the correct date.
Maybe your version of Alteryx has a glitch?
Can you confirm?
Hey guys
Say I'm running my Alteryx after midnight 4/26/2019 but I only want to select dates less than that (i.e. 4/25/2019 and before). How would use Datetimeadd or is that what I should use?
@Tim_at_Ford
Solution - Drop a Select tool before the final Output Data tool and in the date field change Type to "Date", and then change the Size from 19 to 10. This will remove the hh:mm:ss. This will create the format you are looking for in Excel.
I hope this helps