How to get automatic todays year into a formula

I am running a membership organization, we have “Member Since” that is a year, e.g 2014, 2018, etc.
I want to calculate on a column to the right, how many years they have been member.
Something in simple words like =TODAYS YEAR - “Member Since” (in the picture it is “PGM Since”)
So that this adapts to every year. Otherwise I can just keep it to =2020-“Member Since”
Anybody can help with that? :smiley:

Screenshot 2020-08-25 at 13.17.56 How to get automatic todays year into a formula

Try this formula:


Unfortunately it does not work, it says error. :frowning: The column PGM Since, is a “number” not a “date” maybe that why?

We have the specific date, but not for all our members, since we just switched to a new more comprehensive data tracking system. So I prefer using just the yearto at least get somewhat of an idea how long they have been members.

Thanks though! I hope someone else can give me some options that could work!

The formula definitely works, so I’m not sure what’s going on with the error.

There must be something else going on in your base that is preventing @ScottWorld’s formula from working. Can you provide a few more screen captures or share your base?

Thanks for your follow up! I think this is sufficient:

It still does not work in here as well.

Thank you for the shared link to your table.

  • Have you changed the names of your fields since your original screen capture? If so, make sure that you adjust the formula to reflect the current names of your fields.

  • If you have a formula field with just the first part of the formula YEAR(TODAY()) does it work?

  • Originally your year was a lookup field. Formula fields sometimes have unexpected results with lookup values. Sometimes converting the lookup value into a string and then back into a number helps:

    YEAR(TODAY()) - VALUE({PGM (YEAR)} & "")
  • If none of these suggestions help, I recommend contacting customer support.

1 Like

YEAR(TODAY()) - VALUE({PGM (YEAR)} & “”) worked!! :smiley:

Thank you so much!

The original was not a look up :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.