Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

How to get automatic todays year into a formula

Topic Labels: Formulas
Solved
Jump to Solution
530 7
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions

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.

See Solution in Thread

7 Replies 7

Try this formula:

YEAR(TODAY())-{PGM SINCE}

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: https://airtable.com/shryQPChJB9SecTwW

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.

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: