Calculate Years Dynamically

Platform discussions
,
1

Hey everyone! Im new here so hello and thanks in advance.

I would like to get help to calculate years between the end of a contract and today date, but dynamically. I mean, that the result changes while months go on.

I have this formula: YEAR({Fin Contrato}) - YEAR(TODAY())

But the result will stay like this for ever. No changes.

I tried the combo Date and Numeric, but the result comes in days which is uncomfortable.

Any help will be appreciated.
Thanks, Marcelo

2

Hi Marcelo,

I want to be totally clear:

Do you want the result to change or not?

With a Formula, it will change as the years progress.

3

I absolutely want it to change.

For example, I have a group where I have Starting Date column, Ending Date column, and I want to dinamically see how many years left I have. The only thing is that Ending Date is a Formula too. I only populate how many years the contract lasts, and the Starting date changes to TODAY when I create the item.

Many thanks!

4

I don’t fully understand the problem. So presumably you have a formula for your end date. That’s fine and it can stay as that date. Why is a separate formula calculating the years difference an issue? Is it not parsing the date correctly? Would you care to share some screenshots?

Everything seems logical and correct to me. It sounds like you should be receiving expected behavior.

What I’m trying to understand is… what is not working as you expect?

5

Screenshot 2023-05-25 at 21.17.59
Screenshot 2023-05-25 at 21.17.591044Ă—696 57.6 KB

Sure, when the item is moved from other Board, The column Date Firmado (Signed Date) is automatically set to Today. Then I complete manually Duración (Duration) and Fin Contrato (Contract Ending) is a formula column FORMAT_DATE(DATE(YEAR({Date Firmado})+({Duracion}), MONTH({Date Firmado}), DAY({Date Firmado})), “D MMM, YYYY”).

Then Restan (Lasting) should show the lasting years. I use this formula: YEAR({Fin Contrato}) - YEAR(TODAY()). The thing is when the year changes to 2024 the result will not decrease, just stay like it is, because it’s the result of a formula. Does not change automatically.

I assume this because in the following column Dias (Days) I use this formula: ROUND(DAYS({Fin Contrato}, TODAY()), 0). And after a couple days the result stays as it is, not decreasing.

I would need both Restan (Lasting years) and Dias (Lasting days) to update every year and day automatically.

Hopefully I gave you a clear explanation!!

Thanks a lot.

6

I think you’re mistaken.

For example, I just tested this with a simple formula:

FORMAT_DATE(TODAY(),"HH:mm")

And whenever I refresh the page, it shows an updated time. I do need to refresh the page, however the result changes. So I think you must be mistaken somehow. The formula result is live—not static.

I suggest that you keep monitoring this over a number of days. It’s possible that the rounding is throwing you off, as you may get something different to what you expect. It may not be changing when you expect it to, for instance.

7

Hello Francis, and many thanks. I will do that. Of course with the year column I will have to wait, hehe, but for the days it’s going to be easy. Also if that works as you say, probably I didn’t refresh. Just waited to the day to finish and nothing changed. Refreshing is necessary.

8

Looks like you were right! The value changed after a day. Curiously, not all of them changed. I assume its because of the hour, not only the day.

Anyway, problem solved. I only needed to refresh, and as I said wait a day till it changes, and not only with the real date changes.

Thanks a lot!

1 Like
9

image

replace {hire date} with the “contract date” column

INT(YEARFRAC(FORMAT_DATE({Hire date}), FORMAT_DATE(TODAY()))) & " Years " &
INT(MOD(YEARFRAC(FORMAT_DATE({Hire date}), FORMAT_DATE(TODAY())), 1) * 12) & " Months " &
INT(MOD(YEARFRAC(FORMAT_DATE({Hire date}), FORMAT_DATE(TODAY())) * 12, 1) / 12 * 365) & " Days"