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!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

End of Month (EOMONTH) DateTime Formula

Working in the accounting department, this has come up too many times now to ignore! 

 

Would LOVE LOVE LOVE to see a new formula available in the DateTime formula suite that mimics the function of the EOMONTH() formula when working with dates in Excel. 


The beauty of the EOMONTH() formula in Excel is that I can just give it a date, and then tell it how many months in the future or past I would like it to add/subtract... Alternatively, in Alteryx, this can require 2 or 3 nested DateTime functions to arrive at the same answer. 


Example: To find the end of the month 2 months in the future from today's date, I would use the following formula...

Excel = EOMONTH(Today(),2)

Alteryx = DateTimeAdd(DateTimeAdd(DateTimeTrim(DateTimeToday(),"month"),3,"months"),-1,"days")

 

Seems much more complicated than it needs to be in Alteryx, and easy to get lost in the nested formulas & non-intuitive adding/subtracting of months/days! I can see a new formula (something like DateTimeEOMonth?) being structured as follows in Alteryx: DateTimeEOMonth([Field],increment)

 

Please consider! Our accounting department thanks you heartily in advance... 🙂

 

Cheers,

NJ

11 Comments
RachelW
Alteryx Alumni (Retired)
Status changed to: Under Review

@NicoleJohnson Thanks for adding this idea to the ideas forum. I've been wanting to add to the list of functions available in the expression editor. I'll add this one to my list to consider for future improvements. 

Inactive User
Not applicable

DateTimeTrim(DateTimeAdd([Date],2,'months'),'lastofmonth')

Lin_dup_192
6 - Meteoroid

Thank you Ryan.   My accounting team in depreciation uses this function in Excel a lot.   Now I needed for Alteryx.  Very grateful.

ISUGraber
8 - Asteroid

This ever go anywhere?

Second that.

mostany
5 - Atom

Has there been any update on this?

akmalfarid
7 - Meteor

Try to use it in a Formula tool

 

DateTimeTrim([Date field],"lastofmonth")

 

akmalfarid_0-1614620683087.png

 

Matt_S
7 - Meteor

DateTimeLastOfMonth() seems like it should also work as a simpler solution; however, it doesn't accept a variable in the (). It simply shows the EOMONTH for today.

tmlmark
7 - Meteor

@akmalfarid Confirmed, that worked:

DateTimeTrim([Date field],"lastofmonth")
NicoleJ
Alteryx
Alteryx
Status changed to: Not Planned

With this existing formula function, no need for implementing a new one! 

 

DateTimeTrim([Date field],"lastofmonth")