Hi guys,
is it possible to display difference between two dates using formula field? Ideally would be a format: YEARS MONTHS. For instance, “1 year 2 month”. Decimal places using years also works.
Probably a good time to point out that Fibery’s date formula support is pretty poor, and I find myself having to do complex workarounds for things like this, while there are very complete and effective date libraries out there that have mostly solved this problem. Combined with missing other typical functions you’d expect like ceiling, floor, min, max, mod, format, etc it makes things even more difficult.
Unfortunately, I believe the only way to approximate this is to use a combination of ToDays(DateEnd - DateStart)/<some fixed value>. Because there is no mod function, you probably will have one very complicated function, or will need a couple functions pieced together to separate the year portion from the months portion.
I wasn’t able to apply the date filtering I needed within views, so I created a date type that I associated with some entities, which includes some calculated fields. For example, Months from Now and Years from Now.
So, that is
ToDays([Period Start] - [Period End])/30.0 as a decimal formula for Months from Now, then I use [Months from Now]/12 for the Years from Now formula. This isn’t going to be perfectly accurate though, but at least gives me some more comprehensive relative date filtering so that I can have a roadmap that always is based on now through some number of future months plus my “other” buckets.
1 Like