Round a date to the nearest month
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @DGK1981 ,
Further to Mark's post, See attached example. This should get you started.
Regards,
Aidan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, very helpful article and solutions. Problem is now solved
David.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
