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?
Solved! Go to Solution.
@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?
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?
@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’)
This is helpful, thank you!!