Find the previous months for a fix month

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?

Date Time

Accepted answers

binu_acs

@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’)

All comments

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

@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!!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703