We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert dates to last day of given month

jamesbodolay
7 - Meteor

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. 

2 REPLIES 2
kelsey_kincaid
12 - Quasar

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
9 - Comet

Hi @jamesbodolay ..

 

You can also use this formula:

 

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

 

 

Last of Month.png

Labels