How to convert yyyymm to the last day of month in the format of dd/mm/yyyy

[JanaZ]

Hi Everyone

How to convert yyyymm to the last day of month in the format of dd/mm/yyyy? also convert yyyymm to MMMYYYY. 

For example

1. 201909 to 30/09/2019

2. 201909 to Sep2019

I used 

DATETIMEFORMAT(DATETIMEPARSE([Period],'%Y%m%d'),'%d/%m/%Y')

and converted 201909 to 01/09/2019, but when i use

DateTimeTrim([ProcessedDate],"lastofmonth")

it doesnt work. 

thanks

Jana

[Garrett]

I'll bet this all could be done in a single Formula tool, but instead I'd suggest you:

1. Lean on the DateTime tool to handle the lion's share of the conversion syntax for you.
2. Break out the conversion into a couple steps.

[echuong1]

I combined everything into one formula. Let me know if this works for your purpose! 

[JanaZ · Accepted solution]

H Garrett,

Thanks for the solution, however, the LastOfMonth should have been 30/09/2019 instead of 11/30/2019. 

thanks

Jana