Convert dates to last day of given month

[jamesbodolay]

Hello. I have a column containing various dates, all of which are the first day of the month. For example, I have dates such as 2023-01-01, 2026-01-01, 2025-04-01, etc. I would like to convert these dates to the final day of their given months - ie, 2023-01-01 would become 2023-01-31. What is the best way of accomplishing this? I am working on Designer x64. Thank you. 

[kelsey_kincaid · Accepted solution]

Hi @jamesbodolay ,

I would add one month to my date, then subtract one date from that. So, for the '2023-01-01' example you'd add one month, resulting in the date '2023-02-01'. Subtracting one day from that will then get you '2023-01-31'

This formula should do the trick:

DateTimeAdd(DateTimeAdd([Date],1,'month'),-1,'day')

[Rana_Kareem]

Hi @jamesbodolay ..

You can also use this formula:

ToDate(DateTimeTrim([Date], 'lastofmonth'))