Complex calculation related to Date

[ShreyaSharma]

 I actually want is how to write a formula so that it should take last date of every month on formula itself..right now i am not sure how to do it..

means..formula on alteryx should be like

= Last date of every month(static ) - if statement.

[Emil_Kos · Accepted solution]

Hi,

According to this article:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Find-The-Last-Day-Of-Any-Mon...

You can use this formula:

DateTimeTrim([Yout Date Field],'lastofmonth')

[DavidP]

Hi @ShreyaSharma 

There isn't a Last day of month DateTime function (except for the current month), but it's easy to to convert a date to the 1st of that month, so then you can add a month and subtract a day to get the last day of the month.

So if [Date] is 2020-07-21, you can:

1. Extract the Year and Month (making sure Month is 2 digits like 07) and add "-01", like this:  ToString(DateTimeYear([Date]))+'-'+PadLeft(ToString(DateTimeMonth([Date])),2,'0')+'-01', then last day of month is 

  DateTimeAdd(DateTimeAdd(ToString(DateTimeYear([Date]))+'-'+PadLeft(ToString(DateTimeMonth([Date])),2,'0')+'-01',1,'month'),-1,'day')

2. Or, subtract (DataTimeDay([Date]-1) from [Date],add 1 month and subtract 1 day, like this: 

  DateTimeAdd(DateTimeAdd(DateTimeAdd([Date],1-DateTimeDay([Date]),'day'),1,'month'),-1,'day')

[DavidP]

@Emil_Kos  -  that is so much easier! Every day is a schoolday!

[DavidP]

Hi @ShreyaSharma 

You should credit @Emil_Kos with this solution. Please accept his post as Solution.

[Emil_Kos]

Thank you!