Problem with calculating months difference between 2 dates

[triciagdaly]

I am using the following expression to calculate the months difference between today and the EFFECTIVE_DATE, but when the two dates are within the same month, the expression is failing instead of returning 0.  Any ideas?

year(today(2))*12)+month(today(2))) - (((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

Thanks much!

[hemhund2016]

Hi,

It should work. I have tested it as below.

=((year(today(2))*12)+month(today(2))) - (((year(today()-3)*12)+month(today()-3)))

which is returning zero.

You may need to verify if the Effective date is being null or any other ?

Thanks,

Hemanth.

[sunny_talwar]

May be try this:

=RangeSum(((year(today(2))*12)+month(today(2))), -((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

[triciagdaly]

No, it's not returning the row with the two dates in the same month.  Strange that I can separate this expression into two and then take a difference using fabs(end date-start date) and it works.  But can't do in one expression.

This works but would rather have it in one expression and don't understand why it's not returning the row when it's in one expression.

end date:

year(today(2))*12)+month(today(2))) 

start date:

year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

month difference

[end date]-[start date]

:

[triciagdaly]

disregard the fabs() on the last reply.. do not need the absolute value.

[Anonymous]

Hello,

can't you use an expression like this one?:

=num((today() - EFECTIVE_DATE)/30, '0')

[miikkaqlick]

Hi!

Try this:

((year(today(2))*12)+month(today(2)))-

((year(End)*12)+month(End))

Result:

Br,

Miikka

Climber Finland

[triciagdaly]

Hemantha,

Where is the EFFECTIVE_DATE in your example?

[triciagdaly]

Jose,

This is rounding up always versus when it gets to midpoint in month.

[effinty2112]

Hi Tricia,

Your expression had a lot of superfluous brackets that I've removed plus a couple that were incorrect that I've kept but remarked out to show where they were:

year(today(2))*12

// )

+month(today(2))

// )

- year(EFFECTIVE_DATE)*12+month(EFFECTIVE_DATE)

Anyway here's another expression to try (you can't have too many!)

=12*(Year(today(2)) - Year(EFFECTIVE_DATE)) + Month(today(2)) - Month(EFFECTIVE_DATE))