Round a date to the nearest month

[DGK1981]

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.

[MarqueeCrew · Accepted 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. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Marquee-Crew-s-Guide-to-Dates/ta-p/...  

cheers,

 mark

[Aidan_K · Accepted solution]

Hi @DGK1981 ,

Further to Mark's post, See attached example. This should get you started. 

Regards,

Aidan

[Aidan_K · Accepted solution]

Hi @DGK1981 ,

Further to Mark's post, here is an example to get you started.

Regards,
Aidan

[neilgallen]

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

[DGK1981]

Thank you, very helpful article and solutions.  Problem is now solved

David.

[MarqueeCrew]

@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