Calculate survey time from Metadata

how I can calculate the time between start and end (in exported data excel)

Hi @rahhal,

Would you mind having a look at the post that has been discussed previously. It should solve you issue:

Have a great day!

1 Like

Thanks Kal Lam
but i need to use time from kobo metadata question

1 Like

Hi @rahhal,

If this is your requirement, you could do it as shown in the image below:

In the survey tab of your xlsform:

Data recorded in the KoBoToolbox server:

Reference xlsform:
Start & End Time (Metadata).xlsx (9.7 KB)

N.B. Please note that the time difference calculated in this approach cannot be displayed in the form as you are not able to capture the end time. End time are captured once you press the Save button or the Submit button.

Have a great day!

2 Likes

Hi @Kal_Lam,

Thank you for posting this solution. I have tried this for my project and this calculates the survey duration only for a few surveys. Do you have any idea under what circumstances this formula does not work?

Hi @mahrukhkhan,

Would you mind explaining your issue in detail regarding where the same did not work. This would help us look into your issue a bit deeper. Would appreciate if you could provide some screenshots or share with the community the xls data that has issues.

Have a great day!

Please see attached pictures of 1) XForm 2) Data

Hi @mahrukhkhan,

Would you also mind sharing a screenshot from the DATA>Table. This should give us a much more clear picture.

Have a great day!

Do you mean a picture of the raw data Excel file?

I mean the screenshot from your KoBoToolbox server (DATA>Table).

Hi @mahrukhkhan,

I made a quick test and came to the following findings:

  • Works smoothly with Enketo.
  • Has an issue with the Collect android app i.e. data collected and submitted via Collect android app has an issue.

Maybe @Xiphware would be able to add more to this.

Have a great day!

Dear @Kal_Lam,
A hint:
I think, your solution will not work correctly if the end of the interview falls on the next day (midnight+), e.g. if the enumerator has to go back to the household to finish the interview.
4 questions, please:

  1. Could we use end - start calculation (datetime type) and transform the result to time (taking care of a day part)?
  2. Is the end metadata set in Collect at SAVE or at FINALISATION or at SEND in KoBoCollect?
  3. What was the difference between Enketo and Collect you experienced?
  4. When exactly is the start metadata set? At first OPEN or first EDIT/entry (with save)?
    Thanks in advance.

Whenever doing anything involving two timestamps (eg difference between) I’d suggest using decimal-date-time(), rather than trying to fiddle with the string representation… eg try

delta_days = decimal-date-time(${end}) - decimal-date-time(${start})
delta_hours = ${delta_days}*24
delta_minutes = ${delta_hours} * 60
...

Note, these are all reals, so truncate with int() as desired.

2 Likes