How to Calculate Formulas in Custom records, Form records, and Contact record Extra Fields

How to Calculate Formulas in Custom records, Form records, and Contact record Extra Fields

In Custom and/or Form records:


Let's say you need to calculate the difference between two date fields in a Custom record or a Form record. In this case, dates are handled numerically as a number of seconds past midnight on January 1, 1970.


This the number of seconds between two dates:
when4-when3


This is the number of days:
(when4-when3)/86400+1

Explanation: 86400 is the number of seconds in a day, so “/86400” converts seconds to days. The “+1” is because one would normally count both the first and last day (e.g. a membership from January 1 to January 10 would be for 10 days; since 10-1 is 9, you need to add 1 to it).


You can convert this to years by dividing by the number of days in a year:((when4-when3)/86400+1)/365.25


The field that holds the formula for number of days or years should be an Amount field so that it can accommodate decimal points.
Alternatively, if you’d prefer to round the result to the nearest year, you can do this by either:
a. adding 0.5 to the formula:
((when4-when3)/86400+1)/365.25+0.5
b. surrounding the whole formula with a round function:
round(((when4-when3)/86400+1)/365.25)


In Contact record Extra Fields:


When defining a formula in Extra Fields, it works in much the same way as it does in GYOs and Forms. The same rules apply when calculating formulas in Extra Fields, but the difference is as follows:


When identifying the Extra Fields in the formula, rather than using the Extra Field number (e.g. Extra 1), use the Name in Templates (e.g. First_Date).

    Contact Us

    1-877-399-4645