For some questionnaire/survey-type scenarios, you may wish to track the time it takes the user to fill out a Form, or maybe a section in the Form.
This is done by storing a corresponding start and end date/time value, with these values being set when the user answers the first and last questions in the target Form or section.
Once you have the start and end date/time values, you can then use a DATEDIFF() formula function to calculate the time elapsed in your desired unit of measure.
Step-by-step guide
We're going to use Hidden fields for this example since we don't want the user to see these stored values.
You can also use Text or Date/Time fields if you wanted to display the start and finish date/time values to the user.
We're also assuming you have at least two question fields - one at the start with the data name of "myfirstquestion" and one at the end named "mylastquestion".
1. Add a Hidden field with the data name "starttime" to the start of your Form design, and a similar "endtime" Hidden field at the end of the Form.
2. On these Hidden fields, set the Dynamic Value to be as follows:
starttime field: FORMAT-DATE(NOW({{myfirstquestion}}), 'yyyy-MM-dd HH:mm:ss')
endtime field: FORMAT-DATE(NOW({{mylastquestion}}), 'yyyy-MM-dd HH:mm:ss')
Let's explain what the above formula is doing:
- The NOW() function will return the current date/time at the time of the function being triggered
- The {{dataname}} parameter means that the NOW function will be triggered whenever an answer is set into the named field.
- The FORMAT_DATE() function is needed because Hidden fields store text - not date - values.
So if you didn't do this, you would likely lose the time portion of the NOW value. - The formatting parameter ensures the date/time is captured as text like: "2016-10-21 14:10:55"
Also, a worthy mention is that most .NET Format strings/specifiers can work with our FORMAT-DATE() formula function for added customization of captured Dates and Times.
Here is a link to .NET Format strings that may be of interest:
https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings
3. The final piece to this solution is to add one more field (Hidden, Text or Number) which has a Dynamic Value formula to calculate the difference between the finish and start date/time values.
4. This field's formula would be :
DATEDIFF(({{starthidden}}), {{finishhidden}}, 'SS')
This will give you the difference in seconds between the start and end times.
You can of course then divide this value by 60 as desired to get a minute value.
If you wish to get the difference in minutes, hours etc, simply change the 'SS' parameter as needed.
Date/Time Difference Example App
To help you get acquainted with FORMAT-DATE(), DATEDIFF() and NOW() concepts, we've created an example app which we've published to our Examples Catalog.
The Example App consists of 4 pages with various fields to achieve the capturing/displaying of data in different ways.
► Page 1 - Introduction + Capture Start time of form
► Page 2 - Date Difference
► Page 3 - Time Difference
► Page 4 - Capture End time of form + Summary
Using the above mentioned formula to calculate date/time differences, the form demonstrates how to achieve similar functionality with other forms you create.
Learn more about our date/time and data conversion formulas in our Formula Cheat Sheet
To install the Date/Time Difference Example App:
Click here if your account resides on our US Server Node
