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?
Solved! Go to Solution.
Question.
What would the result be if the date was 2018-03-30, would you then class this as 6?
Ben
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)
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
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
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.