How to get automatic todays year into a formula

[EYES_Internatio]

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?

How to get automatic todays year into a formula

[ScottWorld]

Try this formula:

YEAR(TODAY())-{PGM SINCE}

[EYES_Internatio]

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!

[ScottWorld]

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

[kuovonne]

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?

[EYES_Internatio]

ScottWorld:

YEAR(TODAY())-{PGM SINCE}

Thanks for your follow up! I think this is sufficient: https://airtable.com/shryQPChJB9SecTwW

It still does not work in here as well.

[kuovonne · Accepted solution]

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.

[EYES_Internatio]

kuovonne:

YEAR(TODAY()) - VALUE({PGM (YEAR)} & "")

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

Thank you so much!

The original was not a look up :slightly_smiling_face: