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 Knowledge Base

Definitive answers from Designer Desktop experts.

How To Find The Last Day Of Any Month In Alteryx

ChadM
Alteryx Alumni (Retired)
Created

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

Comments
davidhenington
10 - Fireball
Hey Chad, I feel stupid for asking but...what if I want the first day of the month relative to whatever date is in a particular field?
RodL
Alteryx Alumni (Retired)

Use the DateTimeFirstOfMonth function.

Harsha
6 - Meteoroid

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

davidhenington
10 - Fireball

Thanks Harsha! that's even easier than what i came up with! 

dsouthh
7 - Meteor

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
7 - Meteor
hi @ChadM,
why if I insert today this line in formula tool Datetimeadd(DateTimeLastOfMonth(),-1,'month') obtain the result like 2018-03-30 23:59:59 ? the last day of month in march is 2018-03-31. This error is the same for the other month with 31 days!!!
Karlo
8 - Asteroid

@Ferrari I have just viewed your message and for the month of August I am getting the correct date.

Lastmonth31.JPG

 

Maybe your version of Alteryx has a glitch?

Can you confirm?

Tim_at_Ford
7 - Meteor

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?

Padminid
5 - Atom

@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