DateTimeDiff in months

[Anantv]

I am calculating the aged status with DateTimeDiff ().

DateTimeDiff(DateTimeTrim([Month_YR],"months"),DateTimeTrim([DateTime_Out],"months"),"months")

The difference between two dates 2018-03-31 and 2017-09-01 need to evaluate to 7 months whereas the formula results in 6 months. How do I fix this?

[BenMoss]

Question.

What would the result be if the date was 2018-03-30, would you then class this as 6?

Ben

[CharlieS · Accepted solution]

@Anantv

One rough workaround is to take the difference in days, divide by average days per month, and round to the nearest whole month. Here's the formula for that:

round(DateTimeDiff([Month_YR],[DateTime_Out],"days")/30.4,1)

[BenMoss · Accepted solution]

IF, the above is true, then you could apply the following formula to your 2nd date...

if 
datetimetrim(datetimeadd([DateTimeOut],1,'days'),'month')=datetimetrim([DateTimeOut],'month')
then [DateTimeOut]
else datetimeadd([DateTimeOut],1,'days')
endif

and then perform your difference calculation which for 2018-03-30 would return 6 and 2018-03-31 would return 7.

However, then what happens if the first date shifts, say to 2017-09-02, would this then affect the 2nd piece, is this even possible with your data?

Ben

[Anantv]

Ben,

Month_YR filed would always be the last day of the month. 04-30-2017, 03-31-2018, 02-28-2017, etc. This data becomes available at the beginning of the next month. For example, 04-30-2018 file contains a snap shot of all the work orders at the end of April.

Datetime_Out represents the last time the work order was charged. 

I will try your solution to convert it to days. 

Thank you

[Anantv]

Ben, Charlie,

Thank you for your solutions. I was able to get the desired result by adding +1 at the end of the formula.

DateTimeDiff(DateTimeTrim([Month_YR],"months"),DateTimeTrim([DateTime_Out],"months"),"months")+1

I appreciate your time in responding to my query.