For some questionaire/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 data name of “myfirstquestion” and one at the end named “mylastquestion”.
1. Add a Hidden field with data name of “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’)
Lets 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