I am trying to get a field to show the age of a company. I have the year the company started. How do I get an equation to subtract the startup year from the current year resulting in a round number for age?
1 Like
Hey Travis
Great question!
To calculate the age of a record, what you need is the field for the startup year and a field for the calculation.
The best way of calculating this would be to use a conditional equation, which will avoid bad data by making sure there is a value stored in your “Start Year” field.
Use the following formula, replacing “StartYear” with the name of your field:
{StartYear} > -2208988800 & {StartYear} != 0 ? currentTime() - {StartYear} : 0
The long negative number (-2208988800) is the epoch timestamp of 1/1/1900. This is required as blank date values, when used in an equation, are treated as 1/1/1970, the timestamp used as Knack’s “start” date for date values. This equation ensures that any date prior to 1/1/1970 are evaluated the same as values after that date.
Hope this helps! You can read more about calculating ages in our Knowledgebase article, or feel free to ask any further questions!
Thank you so much!
I put in this
{EtableringsĂĄr} > -2208988800 & {EtableringsĂĄr} != 0 ? currentTime() - {EtableringsĂĄr}: 0
”Etableringsår” is established year, which is a number filed for year only, like 2008. But, the result for a company started in 2008 is -718.
What could be wrong?
Hmmm, it could be the equation settings. I did not specify those for you above. They should be:
- Set the Equation Type to “Date.”
- Set the Date Type to “years."
- Make sure the Result Type is set to “Number,” which is the default.
The article on our knowledgebase can be found here
Aaah spot on, thanks @CarlHolmes . I totally missed this VERY important detail.
@TravisLind38612 let us know if this works for you please. Alternatively, we can work on a separate field to extract the current year as a number and work it out like that if that is better for you - it would rely on more formulae so Carl’s response is the best idea as it reduces the complexity of your table so would result in greater performance.
1 Like