Scenario
You would like to calculate someone’s age based on their birthday.
Requirements
Assuming that you have already set up a table to store the birthday value, you can find more information on adding tables here.
Additionally, this article utilizes fields. For more details on adding and managing fields, you can refer to this resource.
Steps
Add a Date Field for the Birthday
To store the value for the birthday, add a Date/Time field type to whichever table you want to store the birthday. Navigate to the Data section of the Builder and add a new field. In this example, we will add the date/time field to the Contact Table with the following settings:
-
Name: "Birthday"
-
Required: yes
-
Default Date: "None"
-
Time Format: "Ignore Time"
You can read more about date/time fields here.
Add an Equation Field to Calculate Age
Next, we need to add an equation field type to calculate the age using the Birthday field we just added. Navigate to the Data section of the Builder to add a new field.
In the same table you added your Birthday field, add an equation field type with the following settings:
-
Name: "Age"
-
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.
-
Use the following equation, which subtracts the birthday from the current date: currentTime() - {Birthday}
This will only work for birthdates after 1/1/1970. For handling dates prior to 1/1/1970 as well, please see the "Using a Conditional Equation" section below.
-
Be sure the rounding is set to "Round Down," so your users aren't having their birthdays before they've actually happened in real-time.
Your Equation field should look like this:
Fields are processed from top to bottom in tables. Be sure to place your equation field below your date/time field that is included in the equation.
You can read more about equation fields here.
Test the Equation
Now that you have added the "Birthday" field and the "Age" field to your table, it's time to test your equation. Navigate to the Records section of the Builder and select the table you added your fields. There you will be able to see your new fields with your Age calculation.
Optional Features
Using a Conditional Equation
If you would like to make your Birthday field optional or to add an extra layer of protection against bad data, you can use the following conditional equation:
{Birth Date} > -2208988800 ? currentTime() - {Birth Date} : 0
Another option would be to use the following conditional equation:
{Birth Date} > -2208988800 & {Birth Date} != 0 ? currentTime() - {Birthday} : 0
This allows us to ensure that there is actually a value stored for the Birthday Field before running the calculation. If there is no value for the Birthday field, we set Age in Years to zero.
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, those values 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 is evaluated the same as values after that date.