Formula Using DATEADD with date field and a linked record not calculating right?

#1

Trying to get dateadd formula to work with a linked record - does this work? In one table "Training Types (names as the fixed field called “Class Name”). One of the other fields in this table is a column called “Certification Good For” and it is formatted as an integer and sets the duration (in years) that each type of training is good for (IE crane safety good for 3 years).

In another table, I have records of all the training classes we offered and the date they were offered at. The first field is a linked record back to “Training Types” so you can select what the training was, then the instructor that gave the course and the date the course was on. There is then a lookup field that references the “Class Name” and looks back to the “Certification Good For”. I am trying to add “Class Date” + “Certification good for”. “Class date” is a date field with 12hr time format, “Certification Good For” is the lookup field. Next field is “Certification Expiration”, so for crane safety which is good for 3 years the class held 3/1/19 should pull in a lookup value of 3 and then add 3 years to 3/1/19 giving an expiration date of 3/2/2022.

Problem is, I just get the expiration as 3/1/2019 (the date of the class from “Class Date Field”). If I change that “Certification Good For” field back to just a number field then the calculation works fine and the “Expiration” field shows 3/2/2022, but not when it is a lookup field (lookup field and the field it references are both correctly formatted as numbers).

Can I not use the looked up value in an equation? I can manually enter the duration of the certification but that kind of defeats the autofill purpose of the base we are making. Anything I am missing here? formula in the “Certification Expiration Date” field is:

DATEADD({Date of Class}, {Certification Good For (Years)}, ‘years’)

Thanks,

0 Likes

#2

HI @Spencer_Mullaney - a lookup field doesn’t return a string, but an array, so you can turn it into a string by concatenating it with an empty string - ''

DATEADD({Date of Class}, '' & {Certification Good For (Years)}, ‘years’)

I haven’t tested the above, but I have a similar formula that works.

JB

0 Likes

#3

Coming through in the clutch again, thank you, the concatenating worked.

For reference I guess, does airtable automatically recognize the string as being a number since concatenating should change the array to a string, but a string (as I usually understand them) should not be able to have math done on it (text not number).

Thanks again,

0 Likes

#4

Sometimes, but I’m not sure it’s guaranteed every time. For accuracy, it’s probably best to wrap the string inside the VALUE function, which will guarantee that you get a number:

DATEADD({Date of Class}, VALUE('' & {Certification Good For (Years)}), 'years')

0 Likes