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.
Solved! Go to Solution.
@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.
cheers,
mark
Hi @DGK1981 ,
Further to Mark's post, See attached example. This should 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
David.
@DGK1981 ,
thank you for asking the question and for marking multiple responses as accepted solutions. Years ago, @chris_love posted a help versus do discussion. He was the helper and I was the doer. Full circle now, while I enjoy the doing I also like the explanation and thought process.
learning to apply Alteryx to a thought process will accelerate your abilities. Community now has a breadth of KB articles and posts available for you to review. I think that the explanations will have a much longer half-life than the workflow solutions. A good mix of both will endure the longest.
@TuvyL @LeahK
cheers,
Mark