Find the previous months for a fix month

[wendyzjy]

hi Experts,

Closing Period is a fix date. I want to create a Month field to get the closest 6 months from closing period. eg, if creation period is 2022/11/01, then Month should be 1, if creation period is 2022/10/01, then Month should be 2. I would only need 6 months data, so other than 6, the value could be any thing bigger, above column A & B is input and Column F G H is the output i expect. I was stuck on the date formatting, could you kindly help?

[binu_acs]

@wendyzjy Use the DateTimeParse([Date],’%Y/%m/%d’) to convert your date fields in to date type. 

Can you explain how you calculated 7 months for the date 20015/4/1?

[wendyzjy]

i used this formula, but get all result as 7, well it shoud not be. at least, there should be some 1 to 6...maybe -1 is not apply to month but date? How can i change it?

[binu_acs · Accepted solution]

@wendyzjy your formula is not correct thats why you are getting 7. Do you want to take the month difference between the closing period and creation period date? 

DateTimeDiff(DateTimeParse([Closing period],’%Y/%m/%d’),DateTimeParse([Creation Period],’%Y/%m/%d’),’month’)

[wendyzjy]

This is helpful, thank you!!