Calculate Interview Duration in XLSForm
To record interview duration in XLSForm, add a start row and an end row to the survey sheet and subtract the two timestamps after export. To get the duration in minutes while the form is still open, stamp the last question with now() in a trigger calculate, then use (decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440. Each example below compiles with pyxform and ODK Validate, the same check ODK Central runs when you upload a form.
How do I record when an interview started and ended?
Add the metadata rows. They have no label, the enumerator never sees them, and the device fills them in:
| type | name | label | parameters |
|---|---|---|---|
| start | start | ||
| end | end | ||
| today | today | ||
| audit | audit | track-changes=true | |
| text | resp_name | Name of respondent |
startis the date and time the form was opened, for example2026-11-19T09:45:10.854+06:00.endis the date and time the form was last saved.todayis the date the form was opened.auditwrites a separate log of every screen visited. It is covered in the last sections of this page.
In the exported data, duration is end minus start. That is enough for many surveys, and it needs no calculation in the form.
end minus start is clock time, not interview time. If the enumerator saves a draft and finishes it the next morning, the duration is 20 hours. If a supervisor opens the form to review it, end moves again. Both timestamps also come from the device clock, so a phone with the wrong time gives wrong or even negative durations.
Why is ${end} - ${start} blank inside the form?
Because end is only written when the form is saved. While the enumerator is still answering questions, ${end} is empty, so a calculate that reads it has nothing to subtract. A calculate with ${end} - ${start} compiles without any error, but it cannot show a duration or drive skip logic during the interview, and users on the ODK forum report it still empty in the submitted data.
The fix is to take your own timestamp at the last question, which is the next example.
How do I calculate the duration in minutes inside the form?
Store the time when the last question is answered, then subtract start from it:
| type | name | label | calculation | trigger | relevant |
|---|---|---|---|---|---|
| start | start | ||||
| text | resp_name | Name of respondent | |||
| integer | hh_size | How many people live in this household? | |||
| select_one yes_no | recontact | May we contact you again for a follow-up visit? | |||
| calculate | finish_time | now() | ${recontact} | ||
| calculate | duration_min | if(${finish_time} = '', '', round((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440, 1)) | |||
| note | show_duration | This interview took ${duration_min} minutes. | ${finish_time} != '' |
| list_name | name | label |
|---|---|---|
| yes_no | 1 | Yes |
| yes_no | 0 | No |
How it works:
triggertells the calculate when to run. With${recontact}in the trigger column,now()is stored at the moment the last question is answered, and not before.decimal-date-time()turns a timestamp into a number of days since 1 January 1970, with the time of day as the decimal part. Subtracting two of them gives days.* 1440converts days to minutes (24 hours of 60 minutes). Use* 86400for seconds or* 24for hours.- The
if()keeps the result empty until the last question is answered, so the note never showsNaN.
A plain now() in a calculate, without a trigger, does not work for this. ODK re-evaluates it when the form is opened, when answers change and when the form is saved, so its value keeps moving. More on decimal-date-time() is in date calculations in XLSForm.
How do I time one section of the form?
Take one timestamp just before the section and one at its last question. This times a food consumption module:
| type | name | label | calculation | trigger |
|---|---|---|---|---|
| text | hh_head | Name of household head | ||
| calculate | food_start | if(${food_start} = '', now(), ${food_start}) | ${hh_head} | |
| integer | days_rice | In the last 7 days, on how many days did the household eat rice? | ||
| integer | days_pulses | In the last 7 days, on how many days did the household eat pulses? | ||
| integer | days_meat | In the last 7 days, on how many days did the household eat meat or fish? | ||
| calculate | food_end | now() | ${days_meat} | |
| calculate | food_minutes | if(${food_end} = '', '', round((decimal-date-time(${food_end}) - decimal-date-time(${food_start})) * 1440, 1)) |
The start stamp is triggered by the question just before the section. Its calculation, if(${food_start} = '', now(), ${food_start}), keeps the first time: if the enumerator goes back and corrects the name later, the stamp does not move. The end stamp uses a plain now(), so it holds the last time the final question was changed.
Both patterns come from the "lightweight timestamping" section of the ODK documentation. Use them for two or three sections you care about. To time every question, use the audit log instead.
How do I flag interviews that were too short?
Do not block the form. An enumerator who is told "minimum 20 minutes" learns to wait, and the duration stops telling you anything. Ask for a reason instead, and review the reasons:
| type | name | label | calculation | trigger | relevant | required |
|---|---|---|---|---|---|---|
| start | start | |||||
| text | resp_name | Name of respondent | yes | |||
| select_one yes_no | recontact | May we contact you again for a follow-up visit? | yes | |||
| calculate | finish_time | now() | ${recontact} | |||
| calculate | duration_min | if(${finish_time} = '', '', round((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440, 1)) | ||||
| text | short_reason | This interview took ${duration_min} minutes. Why was it shorter than 20 minutes? | ${duration_min} != '' and ${duration_min} < 20 | yes |
The question appears only when the duration is under 20 minutes. Real reasons show up quickly: the household had one member, the respondent refused half the modules, the form was a test. Set the threshold from your pilot, at about half the median duration.
Short interviews are one signal among several. Enumerator performance monitoring covers the others.
How do I show the duration as hours and minutes?
Round the duration down to whole minutes, then split it with div and mod:
| type | name | label | calculation | trigger | relevant |
|---|---|---|---|---|---|
| start | start | ||||
| text | resp_name | Name of respondent | |||
| select_one yes_no | recontact | May we contact you again for a follow-up visit? | |||
| calculate | finish_time | now() | ${recontact} | ||
| calculate | total_min | if(${finish_time} = '', '', int((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440)) | |||
| calculate | dur_hours | int(${total_min} div 60) | |||
| calculate | dur_mins | ${total_min} mod 60 | |||
| note | show_hm | Time since the form was opened: ${dur_hours} h ${dur_mins} min. | ${finish_time} != '' |
An interview of 95 minutes shows as 1 h 35 min. Division is div in XLSForm, never /.
How do I calculate duration from the exported data?
Subtract the two columns in your analysis tool. In Excel, with start in column A and end in column B, this gives minutes:
=(DATEVALUE(LEFT(B2,10))+TIMEVALUE(MID(B2,12,8))
-DATEVALUE(LEFT(A2,10))-TIMEVALUE(MID(A2,12,8)))*1440
The formula reads the date (first 10 characters) and the time (8 characters from position 12) of each timestamp and ignores the time zone at the end. That is safe when both stamps come from the same device, which they do.
Before you average the result, look at its distribution. Drop or check durations that are negative (the device clock was changed) and durations of many hours (the form was saved and finished later).
Which is more reliable: start and end, or the audit log?
The audit log. The ODK documentation says so directly: to reliably measure how long a survey took, use form audit logging, because start and end depend on the device clock. The log lists every screen with the time it was opened and left, so you can add up the time actually spent on questions and leave out the pause between a saved draft and its completion.
| Method | What it measures | Weak point |
|---|---|---|
end minus start | Clock time from first open to last save | Includes breaks, drafts and later edits |
now() with trigger | Clock time up to a chosen question | Still includes breaks; moves if the answer is changed |
audit log | Time on each screen | A separate file that needs processing |
The audit row costs nothing to add, so add it to every form even if you only plan to use start and end. The ODK audit log explained shows what the file contains.
DataSnap reads the audit log for you. It reports active interview time per submission, with long gaps between screens left out, and flags interviews that are much quicker than the rest of the team.
Common mistakes and how to fix them
These are the messages pyxform and ODK Validate return, which is what you see when ODK Central or KoboToolbox rejects a form:
| You wrote | Write instead | Error you will see |
|---|---|---|
${end} - ${start} in a calculate | Stamp the last question with now() and a trigger | No error, but the result is empty during the interview |
decimal-date-time(${start}) with no start row | Add a row with type start and name start | Could not find the name 'start' |
decimal_date_time(${start}) | decimal-date-time(...) with hyphens | cannot handle function 'decimal_date_time' |
(...) * 1440 / 60 | (...) * 1440 div 60 | Unexpected beginning of path in expression |
recontact in the trigger column | ${recontact} | Reference variables must start with '${', then a question name, and end with '}' |
| A trigger naming a question that does not exist | Use the question's exact name | the 'trigger' value is invalid ... Could not find the name 'last_q' |
Two rows named start | Keep one | the 'name' value 'start' is invalid. Questions, groups, and repeats must be unique |
decimal-date-time(${finish_time}) - decimal-date-time(${start}) with no * 1440 | Multiply by 1440 | No error, but 18 minutes shows as 0.0125 |
For other messages, see common XLSForm errors.
Does this work the same in ODK, KoboToolbox and SurveyCTO?
The start and end metadata types exist on all three. How you time things inside the form differs:
| Platform | What to use |
|---|---|
| ODK (XLSForm) | start, end and audit rows. In-form timing with now() in a calculate and the question in the trigger column, as on this page. |
| KoboToolbox Formbuilder | Tick start time and end time in the form's metadata settings. KoboToolbox describes end time as the moment the form is finished, and it changes again if the submission is edited later. |
| KoboToolbox (XLSForm upload) | Same rows as ODK. Test trigger calculations in the collection method you use (KoboCollect or web forms) before fieldwork. |
| SurveyCTO | Has its own duration() function, which returns the seconds spent filling or editing the form. Put duration() in a calculate for the total, or once(duration()) in a calculate_here field to capture the seconds taken to reach that point. Skip logic goes in a column named relevance. |
On SurveyCTO, prefer duration() to subtracting timestamps: it counts time spent in the form, not clock time.
Why duration belongs in your daily checks
Interview length is the cheapest quality signal a survey produces. A 45-minute questionnaire finished in 9 minutes was not asked properly, and you can see that on the day it happens, while the enumerator can still go back. Recording start, end and an audit log takes three rows. Reading them every evening is what turns them into fewer fabricated interviews.
You can describe it in plain language instead: "record the interview duration in minutes and ask for a reason when it is under 20 minutes". LooprAI writes the metadata rows, the trigger and the calculation, adds the audit log, then compiles the form with pyxform before anything is saved.
Next: XLSForm calculation examples →
Frequently Asked Questions
How do I calculate interview duration in XLSForm?
Add start and end metadata rows and subtract them after export. To get minutes inside the form, stamp the last question with now() in a trigger calculate, then use (decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440 in another calculate.
Why is ${end} - ${start} empty in my form?
The end metadata value is only written when the form is saved, so it is empty while the interview is in progress. Take your own timestamp instead: a calculate with now() in the calculation column and the last question in the trigger column.
Why are some of my survey durations negative or many hours long?
Start and end come from the device clock and measure clock time. A negative value means the clock was changed during the interview. A very long one means the form was saved as a draft and finished later, or edited after it was first saved.
How do I convert decimal-date-time() to minutes?
decimal-date-time() returns days since 1 January 1970, so the difference between two timestamps is in days. Multiply by 1440 for minutes, by 86400 for seconds or by 24 for hours, and wrap the result in round() or int().
Can I stop enumerators from finishing a survey too quickly?
You can, but it is better not to block the form, because enumerators learn to wait. Calculate the duration at the last question and show a required question asking for a reason when it is below your threshold, then review those reasons daily.
Is start and end time accurate enough to measure interview length?
It is accurate for clock time between opening and the last save, on a device with the right time. It cannot separate breaks from interviewing. For that, add an audit row: the audit log records the time spent on every screen.
No Credit Card Required
Build the form first. Add hosting when you need it.
Start free with LooprAI, then deploy a managed ODK Central server or run DataSnap checks when your project is ready.
Automation handles the mechanical work.
Researchers decide what the evidence means.