Convert month abbreviated string to last date of month

Question

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 Answer

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 Answer

Please read this article:

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

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 Answer

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