SurveyLoopr
Tips & Tricks
13 min read

Calculate Interview Duration in XLSForm

Mehrab Ali
Mehrab AliPublished November 19, 2026

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.

Download example form (.xlsx)All five examples in one XLSForm, one group each. Tested with pyxform and ODK Validate; free to reuse under CC BY 4.0.Download

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:

typenamelabelparameters
startstart
endend
todaytoday
auditaudittrack-changes=true
textresp_nameName of respondent
surveychoicessettings
  • start is the date and time the form was opened, for example 2026-11-19T09:45:10.854+06:00.
  • end is the date and time the form was last saved.
  • today is the date the form was opened.
  • audit writes 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.

Warning:

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:

typenamelabelcalculationtriggerrelevant
startstart
textresp_nameName of respondent
integerhh_sizeHow many people live in this household?
select_one yes_norecontactMay we contact you again for a follow-up visit?
calculatefinish_timenow()${recontact}
calculateduration_minif(${finish_time} = '', '', round((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440, 1))
noteshow_durationThis interview took ${duration_min} minutes.${finish_time} != ''
surveychoicessettings
list_namenamelabel
yes_no1Yes
yes_no0No
surveychoicessettings

How it works:

  • trigger tells 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.
  • * 1440 converts days to minutes (24 hours of 60 minutes). Use * 86400 for seconds or * 24 for hours.
  • The if() keeps the result empty until the last question is answered, so the note never shows NaN.

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:

typenamelabelcalculationtrigger
texthh_headName of household head
calculatefood_startif(${food_start} = '', now(), ${food_start})${hh_head}
integerdays_riceIn the last 7 days, on how many days did the household eat rice?
integerdays_pulsesIn the last 7 days, on how many days did the household eat pulses?
integerdays_meatIn the last 7 days, on how many days did the household eat meat or fish?
calculatefood_endnow()${days_meat}
calculatefood_minutesif(${food_end} = '', '', round((decimal-date-time(${food_end}) - decimal-date-time(${food_start})) * 1440, 1))
surveychoicessettings

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:

typenamelabelcalculationtriggerrelevantrequired
startstart
textresp_nameName of respondentyes
select_one yes_norecontactMay we contact you again for a follow-up visit?yes
calculatefinish_timenow()${recontact}
calculateduration_minif(${finish_time} = '', '', round((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440, 1))
textshort_reasonThis interview took ${duration_min} minutes. Why was it shorter than 20 minutes?${duration_min} != '' and ${duration_min} < 20yes
surveychoicessettings

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:

typenamelabelcalculationtriggerrelevant
startstart
textresp_nameName of respondent
select_one yes_norecontactMay we contact you again for a follow-up visit?
calculatefinish_timenow()${recontact}
calculatetotal_minif(${finish_time} = '', '', int((decimal-date-time(${finish_time}) - decimal-date-time(${start})) * 1440))
calculatedur_hoursint(${total_min} div 60)
calculatedur_mins${total_min} mod 60
noteshow_hmTime since the form was opened: ${dur_hours} h ${dur_mins} min.${finish_time} != ''
surveychoicessettings

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.

MethodWhat it measuresWeak point
end minus startClock time from first open to last saveIncludes breaks, drafts and later edits
now() with triggerClock time up to a chosen questionStill includes breaks; moves if the answer is changed
audit logTime on each screenA 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.

Note:

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 wroteWrite insteadError you will see
${end} - ${start} in a calculateStamp the last question with now() and a triggerNo error, but the result is empty during the interview
decimal-date-time(${start}) with no start rowAdd a row with type start and name startCould not find the name 'start'
decimal_date_time(${start})decimal-date-time(...) with hyphenscannot handle function 'decimal_date_time'
(...) * 1440 / 60(...) * 1440 div 60Unexpected 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 existUse the question's exact namethe 'trigger' value is invalid ... Could not find the name 'last_q'
Two rows named startKeep onethe 'name' value 'start' is invalid. Questions, groups, and repeats must be unique
decimal-date-time(${finish_time}) - decimal-date-time(${start}) with no * 1440Multiply by 1440No 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:

PlatformWhat 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 FormbuilderTick 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.
SurveyCTOHas 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.

Tip:

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.

Build my first form free

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.