DateTimeDiff in months
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Question.
What would the result be if the date was 2018-03-30, would you then class this as 6?
Ben
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
