Convert month abbreviated string to last date of month

[darenson]

I have various data sources where I need to convert the abbreviated month, listed as: Jan, Feb, Mar, Apr etc... into the last day of the month (preferably in this format) "2019-01-31", "2019-02-28", "2019-31-03", etc.. all the way to December. 

I tried inserting a replace function, but it doesn't seem to be working for whatever reason.

Any help with this would be greatly appreciated.
Thanks 🙂

[fmvizcaino · Accepted solution]

Hi @darenson ,

I created two examples that you can use in your workflow. Personally, I prefer the first one since it calculates all dates automatically in a formula tool without a second support table.

Take a look at the attached example and let me know if that works for you.

Best,

Fernando Vizcaino

[MarqueeCrew · Accepted solution]

Please read this article:

https://community.alteryx.com/t5/Engine-Works-Blog/MarqueeCrew-s-Guide-to-Avoiding-Date-Frustration/... 

i Try to explain the potential use case. You can look for oct 1 2019,  add a month and then subtract a day to get the last day. 

cheers,

 mark

[danilang · Accepted solution]

Hello @fmvizcaino 

As opposed to adding a month and and then subtracting a day, you can use DateTimeTrim([Parse],"lastofmonth").  This returns a DateTime value one second before the end of the month, i.e. 2019-02-28 23:59:59 but if you specify that the output is a Date type the time value will be stripped off.

Dan

[MarqueeCrew]

@danilang ,

 very interesting use of DateTimeTrim(). I never used those functions. You must be aware of data type and output the date type when you really don't care about time. 

i like knowing that it works this way as an option. 

cheers,

 mark

[fmvizcaino]

@danilang ,

I had no idea you could do something like that with trim. 

Before seeing your reply, I was even thinking about submitting that as an idea for Alteryx. 

Phew!!!! 

Thanks for showing me once again an easier way to get things done! 🙂

Best,

Fernando V.