Troubleshooting Duration Calculations Using start and end Survey123 connect

JoseBarrios1

I’m working in Survey123 Connect and I want to capture survey start and end times automatically using the start and end question types. My goal is to have 4 key columns in my dataset:

1. StartTime – human-readable start time in hh:mm AM/PM format (example- 07:30 AM)

2. EndTime – human-readable end time in hh:mm AM/PM format (example- 09:15 AM)

3. DurationMin – duration of the survey in minutes (example- 105)

4. DurationHour –  decimal hours so I can calculate averages (example- 1.75)

When I use time type questions, my formulas (see attached XLSForm) work perfectly. I can calculate duration and format the times without any issues.

However, when I switch to start and end fields to capture timestamps automatically, my calculations break, and I get  type mismatch errors. It seems the datetime values behave differently from simple time/date fields, and I’m unsure how to adjust my formulas to work correctly .

Any examples of formulas or tips to handle start/end data type in Survey123 would be greatly appreciated!

Thank you in advance for any guidance.

 

JB

Code_estimate_Time_duration.xlsx

SarahBell_0-1744052759721.png

JoseBarrios1

Thanks Neal for the info and for sharing the blog link!

The issue ended up being the way Survey123 handles the start and end question types. Those fields store full datetime values, not just time, which is why my original formulas kept throwing type mismatch errors. The calculations needed to be adjusted to handle the datetime format. After a bit of trial and error, I was able to update the formulas so they correctly produce the fields and format I needed.

I'm sharing the final working template here in case anyone else runs into a similar situation.

Thanks,

JB

Code_estimate_Time_Explained.xlsx