Working with date and time fields can be a little intimidating, but once you get the hang of it there are a lot of useful ways you can use date and time calculations in your forms to enhance your data quality. This article is going to focus on how to calculate someone’s age from their birth date.
When you are collecting the date of birth in the current form
Let’s start with the simplest possible scenario, which is when you are collecting the person’s birth date in the current form.
Step 1
First, create a date field to hold the person’s date of birth. For the purposes of this example, we’re going to call that field ‘dob’ (for ‘date of birth’).
Step 2
Next, create a calculate field called ‘age’, and add an expression to calculate the difference between today’s date and ‘dob’. The core of the expression is this:
today() - ${dob}
The today() function returns today’s date according to the time on the device accessing the form, so it’s important to make sure that times and dates are set properly on your enumerators’ devices. The result of this expression will give you the number of days that have passed since the date stored in the ${dob} field and today.
This works because date values have underlying integer values which is the number of days since January 1st, 1970. So, if I were to subtract March 3rd, 1992 (8097 days since January 1st, 1970) from today’s date (e.g. April 20th, 2019 - 17975 days since January 1st, 1970), I would get 9878 days.
For most purposes though, this will not be a number that is useful, so we have to extend the expression to modify this value into a more useful format.
Step 3
To do that, we can add on the following:
(today() - ${dob}) div 365.25
Since there are 365 days in a year, this divides the results of the today() - ${dob} by 365.25. The .25 is a rough way of adjusting for leap years.
Following the example above, that’d be 27.044490075 days.
For a shorter more manageable number, we put the int() function around the entire existing expression to convert that number into an integer by dropping any values after the decimal. So, the result of both int(3.12) and int(3.87) will be 3. That would look like this:
int((today() - ${dob}) div 365.25)
You might also want some decimal values (e.g. two places), so then you might use the round() function instead of int(), like follows. The 2 after the commas is the number of decimal places.
round((today() - ${dob}) div 365.25, 2)
Note that the younger the person is, the value you’ll get by using the 365.25 divisor will be more inaccurate. If you need exact, 100% precision, go to the bottom of this article for advice on how to go about that.
When you are using previously collected date of birth information
If you are using date data from outside this form which is stored in a pre-load source, you might have to go through a number of steps to reconstruct a date-type variable from your existing data before you can use that variable in calculations with other dates.
The function to convert data in string format into date is date() and its syntax is
date('YYYY-MM-DD')
Your dates need to be in YYYY-MM-DD format to before being passed into the date() function for the function to be able to work properly.
Warning: date() will return an error, making it impossible to submit a form, if invalid values are passed into it (discussed in more detail here). This includes scenarios (like option 2 below) where date values might be partial, or might be in the process of being assembled. The trick here is to make any field that has a date() function in it, not relevant until such time that a constructed date value is a valid date value by that time in the form. So, in the example that follows, you might make a calculate field that converts pre-loaded parts of a date into a valid date value, only relevant once the pre-loading has happened.
If you collected the date variable in SurveyCTO and haven’t manipulated it, you can use it as is. However, it’s possible that in your pre-load .csv you have separate columns for day, month, and year values. If this is the case, you have a few potential routes you can follow.
Option 1 - Manipulate the date data outside your form
This is probably the most straightforward option. Process your data in whichever software you prefer so that it is in YYYY-MM-DD format. For example, if working in Excel, if column B contains the 4-digit year, column C contains the 2-digit month, and column D contains the 2-digit day, you can use a concatenate formula, like the one below, to create the variable you need. Note that # is the corresponding row number.
=CONCATENATE(B#;”-”,C#;”-”;D#)
Once you create this formula in the second line, you can click on the little square in the bottom right corner of the highlighted cell and drag it to the bottom (this feature is called AutoComplete in Excel).
If the values in each column are different than highlighted above, you might need to do some extra manipulation.
If you handle the necessary manipulations in the pre-loaded data, it makes things simpler in your survey, because you can then just import it and use it as is.
Option 2 - Manipulate the data in the form
You can also deal with incorrectly formatted date data once it’s already in the form (if you were pre-loading date values into your form in different parts). How you approach this depends on what format your date fields are already in, but I’ll demonstrate one approach for you to give you ideas about how to approach a challenge like this. I am going to break down each step to show you what’s happening, but at the end you can combine a lot of these expressions into one calculate field once you’ve tested your setup and are confident that it’s working. There’s no performance drawback to keeping the calculate fields separate though, and it could make your form easier to read and maintain.
Let’s assume you have your birth date data in three separate columns in your .csv, one each for year, month, and date.
Step 1
First, you’ll need to pre-load the three values. You’ll do this using calculate fields and the pulldata() expression. See our help topic on pre-loading to find out more, if you’re not familiar.
Step 2
For the purposes of this example, let’s call these calculate fields "year_pl", "month_pl", and "day_pl", with “_pl” standing for pre-load. And let’s assume that all of these values were already stored in numerical format (1-31 for the days, 1-12 for the months, four digits for the years).
We need to combine the year, month, and day data to fit the YYYY-MM-DD format so that we can pass it into the date() function. To do this, we’ll use the concat() function in another calculate field to concatenate these values. Let’s call this field "dob_pl".
concat(${year_pl},’-’,${month_pl},’-’,${day_pl})
The result of this will be the three separate values combined into the YYYY-MM-DD format we need.
Warning: please review this troubleshooting article before constructing date values with concat() if you plan on passing these values through the date() function.
Step 3
From here onward, the steps are similar to the scenario where you are collecting date data using a date field in your form design. You’ll have a calculate field that uses this expression”
int((today() - date(${dob_pl})) div 365.25)
This expression will calculate and then format the difference in days between today and the pre-loaded date of birth. Note that I added the data function around ${dob_pl} in this expression, but I could have also put the date() function around the above concat() function instead and the result would have been the same. Keep in mind that there is usually more than one way to accomplish complex tasks like this in SurveyCTO.
When you need real precision, to the exact day
Note that the divisor in the expression below, 365.25.
int((today() - date(${dob_pl})) div 365.25)
This divisor is an estimate meant to take into account leap years. The younger the person is, the more you might need to tweak this value to get to a point where the vast majority of your responses end up with the correct integer for age.
If you are using a formal cutoff by age for enrollment, particularly if it involves children, it might make sense to use day cutoffs rather than years or months for true precision. For example, if you want to only include children up to the age of 3 months, it might make sense to conceptualize 3 months as 90 days (or 91/92 to account for months with 29, 30, or 31 days), or to conceptualize the cutoff value for 2 years as 730 days.
The above features the following functions and operators: date(), today(), round(), concat() and div. Consult the product documentation on using expressions in your forms to read about these and other functions and operators.
Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.
0 Comments