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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Complex calculation related to Date

ShreyaSharma
8 - Asteroid

 

ShreyaSharma_1-1597819350471.png

 

 

 

 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.

5 REPLIES 5
Emil_Kos
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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
17 - Castor
17 - Castor

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

DavidP
17 - Castor
17 - Castor

Hi @ShreyaSharma 

 

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

Emil_Kos
17 - Castor
17 - Castor

Thank you!

Labels