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

Date formatting - last day of transaction month

Lisa_M
8 - Asteroid

Relative newbie here chaps - so thanks in advance for not actually laughing at the question I'm about to ask.  As a heads up, someone has asked this question before, but the solution provided doesn't work (for me), so I'm a bit stumped.

 

My data source is providing a transaction date as '01-APR-2017', which I need to convert to '01/04/2017' - so by trial and error, I've done that bit through this formula:  DateTimeFormat(DateTimeParse([EFFECTIVE_DATE], '%d-%b-%Y'),'%d/%m/%Y')

 

I was practically dislocating my shoulder patting myself on the back to have done this. And then my users said "oh, actually, can we change that to the last day of the month instead?"  Of course, there is a last day of the month formula in Alteryx - but that's the last day of THIS month, not the month the transaction happened in. 

 

So, sorry for all the whining, but my question is how do I convert '01/04/2017' to '30/04/2017' - which would equally apply to other months - so if transaction date was '16/07/2016', it would convert to '31/07/2016'?

4 REPLIES 4
Federica_FF
11 - Bolide

hi @Lisa_M

 

I think the formula you need is:

 

DateTimeTrim([EFFECTIVE_DATE],"lastofmonth")

Lisa_M
8 - Asteroid

Thanks for the reply, but I've tried this and it doesn't work.

gc
9 - Comet

How about this? You may be able to improve it, but I think it gets the job done.

Lisa_M
8 - Asteroid

Okay, I resolved this issue by applying a date time parse to my converted field, and then by layering Federica's formula over the top of that.

 

What a mission!

Labels