Hi all,
I would like to round a date to the nearest month.
For example,
2020-05-01 should round down to 2020-05
2020-05-28 should round up to 2020-06
Any ideas?
Many thanks.
David.
@DGK1981 ,
First you'll have to agree that the rule would be something like: if the day of the month is greater than the 15th, make the date the first of the next month, otherwise make it the first of the present month.
I've written an article about date manipulation that should help you with the logic components for these dates.
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Marquee-Crew-s-Guide-to-Dates/ta-p/156024
cheers,
mark
Hi @DGK1981 ,
Further to Mark's post, See attached example. This should get you started.
Regards,
Aidan
Further to Mark's post, here is an example to get you started.
Regards,Aidan
using the same logic as mentioned but in one formula:
if datetimediff([Field1],datetimetrim([Field1],'firstofmonth'),'days')<=15 ## Find difference between the first day of the month of the date field and the date field then datetimetrim([Field1],'firstofmonth') ##trim date to first day of month if difference is less than 15 else datetimeadd(datetimetrim([Field1],'firstofmonth'),1,'month') ## Trim date to first day of month, but then add one more month to 'round up' endif
Thank you, very helpful article and solutions. Problem is now solved
