Age determination

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).

* Do you have an official age documentation for ${child_name}?
* If yes (from official age documentation):
  * ${child_name}'s day of birth:
  * ${child_name}'s month of birth:
  * ${child_name}'s year of birth:

If no (determination of month and year of birth using a calendar of events):

  * ${child_name}'s month of birth:
  * ${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)


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:


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)

1 Like

Hello @fannette,
another approach might be:

  • use a normal calendar date field
  • if day (or month even) is not known, have a fix rule for the enumerator/respondent what to code: e.g. day 15 (month June?)
  • maybe add a question (select_one or text) where the enumerator/respondent can mark if the answer was estimated or taken from document.

Furthermore, as you want to include all these children under 5 and the answer might not be exact, you may think about additional rules, like if up to 5.4 calculated age, include the case. You might collect more cases first and be free to filter them out later in data analysis.

You should also know how the local/national civil registration registers cases with unknown day (and month). Sometimes in refugee context they use day 01. (And in countries with week civil registration systems do not trust the official documents too much.)


Dear wroos,

Thank you very much for your prompt answer. It is well appreciated.
Your suggested approach was our first approach but after had several discussions with my colleagues, and in order to fllow WHO and UNICEF recommendations (TEAMS recommendations) for nutrition surveys, we would like to keep our current questions and have an date of birth (or estimaed date of birth) automatically generated.

Any suggestion regarding the formula/calculations of the age in months?

In our previous versions we used that:
if(${dob_known}=‘1’,int((today() - ${BIRTHDAT}) div 30.4375),${xmonths})
But we had a normal calendar date field for the DoB.

Thank you

1 Like

Hello @fannette,
could you share a link for this, please.

Pay attention, please. the today() function will always update, for ex. if the interview goes on another day, or if the case is edited later on server level. So, the calculation of the month will change). If you want to keep it stable, you might better use the metadata today ( ${today} ) or create and work with a calculate (MyToday) variable here, using once( today() ). I am not sure, if once will also work in your formula directly.

For your calculation, you might also

  • work with date functions, e.g. ODK XForms Specification, and continue calculation with the difference to the null-date January 1, 1970 UTC.
  • develop a formula relating to what humans do manually and exactly calculating: (difference of years * 12) + (difference of months). This can be implemented with the data (format) functions.
  • ask UNICEF for their age calculation formula used in their MICS study (CSPro).

And - as always recommended - the search function of this forum may help you, see Search results for 'calculate months' - KoBoToolbox Community Forum.

1 Like

Dear wroos,

Thank you for your support.
I’ll let you know later what will be our solution for age determination. That might be useful to ask UNICEF what they use in MICS surveys.

1 Like

Thanks @fannette,
I would like to publish few hints from the WHO document

“Measurement bias can occur where vital registration is not universal and information on the exact date of birth is not available. In this situation the interviewer is obliged to estimate the year and month of birth from incomplete records, maternal recall or by means of local events calendars. Misreporting can be due to the respondent genuinely not knowing a child’s date of birth, faulty date-of-birth records, incorrect handling of the local events calendar by the f ield team or data fabrication. Data heaping is more commonly observed for older (36–48 months) than younger children (12–24 months), most probably due to the fact that caretakers are less good at recalling the birthdates of older children. In some settings, children are issued with vital registration documents, but in populations where an application is not lodged for the child at the time of birth but instead sought some months or years following birth, a specific but arbitrary month—often “January”—is assigned as the month of birth on birth certificates, vaccination cards and other documentation when the true month of birth is unknown. Major data heaping on the month of birth can also be seen when local events calendars are not used according to the recommendations set out in this report …”, page 59, also further hints for QA on age data.

1 Like

Thank you, for this valuable information sir

1 Like

any further information will be grateful

How can I get access to all this code sir?

Welcome to the community, @adeelanwar112211! Could you provide more details on this? Maybe the community should be able to help you out.

big bro I’m too bad with coding stuff I tried so hard even from the beginning. I have hired a developer boy whose job is only to understand the codes.

Thank you, for this valuable information.

1 Like

Thank you Kal_Lam can I also get details as wells? :grinning:

1 Like

really appreciate it

what do you mean with “a costumized field” here?
And how is your “help” link related to this forum and your posting, please??

Hello, Are you talking about the code placement?