Age determination

Support
1

Dear experts,

In our survey questionnaire, we have this set of questions to determine the age of children under five years old (lines 44-48).
1- Do you have an official age documentation for ${child_name}?
2- If yes (from official age documentation):

  1. ${child_name}'s day of birth:
  2. ${child_name}'s month of birth:
  3. ${child_name}'s year of birth:
    If no (determination of month and year of birth using a calendar of events):
  4. ${child_name}'s month of birth:
  5. ${child_name}'s year of birth:

Then, we would like to calculate the age of the child in months using the date of birth (DD/MM/YYYY)
OR
If only the month and year of birth are provided, the missing information for the day of birth will be imputed. We would like to imput the 15th of the month for all missing days of birth.

To calculate the age in months based on above questions we have this formula:
if((${dob_known}='1',DATE(${year_birth},${month_birth},${day_birth}),DATE(${year_birth},${month_birth},15

Our problem is:
The attached ODK questionnaire is not working because the date function is not correct. Does the DATE(year_birth,month_birth},day_birth) function is available in the XLS forms?
Should we use another function like “CONCAT”?

Many thanks in advance for your support
SMART_Survey_ANTHRO_059_EN_FR_07 Bis.xlsx (33.8 KB)

2

There are a couple of ways you can do that.

One is to have a date question with an appearance type that displays only Year and Month ("month-year") but not date. The result in the data will be the first day of the month even though the enumerator will never see the specific day (eg. "2022-02-01") and you can then add +14 if you decide the middle of the month is best and from there calculate the age by subtracting from ${today}. Best if you include the "today" metadata question at the start of the form as well as "start" and "end" etc.

The other way is the date function you are currently using but it's sort of a string function and you would need to probably use a "YYYY-MM-DD" format and insure that February is "-02-", not "2" for example. Personally I would advise the first approach as then you can not worry about date formats.