Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

How to get automatic todays year into a formula

Topic Labels: Formulas
Solved
Jump to Solution
1870 7
cancel
Showing results for 
Search instead for 
Did you mean: 
EYES_Internatio
5 - Automation Enthusiast
5 - Automation Enthusiast

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: