nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Date Time

Accepted answers

CharlieS

@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

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

All comments

BenMoss

Question.

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

Ben

CharlieS

@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

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

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703