Formula to calculate days, month and years sometimes gives errors

Hi,
I want to calculate the years, month and days someone is signed up to our club. In this forum I found some very good help. Unfortunately the formula is not working for all dates and I can’t figure out why. Maybe someone has an idea.

This i my formula:
IF({Anmeldung},
CONCATENATE(INT(YEARFRAC(FORMAT_DATE({Anmeldung}), FORMAT_DATE(TODAY())))," J “,
INT(MOD(YEARFRAC(FORMAT_DATE({Anmeldung}), FORMAT_DATE(TODAY())), 1) * 12), " M “,
INT(MOD(YEARFRAC(FORMAT_DATE({Anmeldung}), FORMAT_DATE(TODAY())) * 12, 1) / 12 * 365),” D”)
,“n/a”)

Here the results

Thanks for any hints.
Bjoern

Hi Bjoern,

I tested the formula and it works fine even with the dates you show with errors:

2024-06-10_07-32-10

The only thing I am noticing in your syntax is that you have inconsistent double-quotes. You have straight and slanted mixed up. I am not sure if that’s a bad conversion caused by pasting your formula in the post or if that’s really what you have in your formula column, but you may want to double-check (I don’t believe your formula would work at all if it is the case, but hey who knows…).


Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

Hi Gilles,
thanks for the hint.
I checked the double-quotes and changed them. The failures are still showing up. It says “One parameter is invalid”.
Any other clues?

It looks like I found the problem.

After I added <, “YYYY-MM-DD”> to the YEARFRAC function, it works now.
YEARFRAC(FORMAT_DATE({Anmeldung}, “YYYY-MM-DD”), FORMAT_DATE(TODAY(), “YYYY-MM-DD”)))

Maybe it was a bug, because I use the German date format and the TODAY function uses another one?